[Database] SELECT Statement | SELECT 문

SELECT Statement

 

SELECT 문

 

- SELECT는 하나 이상의 Table로부터 Rows를 Retrieve하기 위해 사용하며,
  UNION 연산과 Subquery를 포함시킬 수 있다.

- MySQL 8.0.31 버전부터 INTERSECT 연산과 EXCEPT 연산을 지원하기 시작했다.

 

※ 본 포스트는 MySQL 8.0 Reference Manual을 기준으로 작성되었다. (URL)

MySQL :: MySQL 8.0 Reference Manual

 

---

Synopsis (개요)

 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
[FROM table_references
    [PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
    [OF tbl_name [, tbl_name] ...]
    [NOWAIT | SKIP LOCKED]
     | LOCK IN SHARE MODE]
[into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
    | INTO DUMPFILE 'file_name'
    | INTO var_name [, var_name] ...
}

 

- SELECT 문에서는 WITH Clause에 선언된 내용에 접근할 수 있다. (URL)

 

---

SELECT Clause (SELECT 절)

 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]

 

Options of SELECT Statement (SELECT문 옵션)

 

SELECT Options	Description
ALL
DISTINCT
DISTINCTROW
HIGH_PRIORITY
STRAIGHT_JOIN
SQL_SMALL_RESULT
SQL_BIG_RESULT
SQL_BUFFER_RESULT
SQL_NO_CACHE
SQL_CALC_FOUND_ROWS

 

The list of select_expr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use *-shorthand:

• A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables:

• SELECT * FROM t1 INNER JOIN t2 ...

• tbl_name.* can be used as a qualified shorthand to select all columns from the named table:

• SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

• If a table has invisible columns, * and tbl_name.* do not include them. To be included, invisible columns must be referenced explicitly.
• Use of an unqualified * with other items in the select list may produce a parse error. For example:To avoid this problem, use a qualified tbl_name.* reference:Use qualified tbl_name.* references for each table in the select list:

• SELECT AVG(score), t1.* FROM t1 ...

• SELECT id, t1.* FROM t1

• SELECT id, * FROM t1

The following list provides additional information about other SELECT clauses:

• A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:The AS keyword is optional when aliasing a select_expr with an identifier. The preceding example could have been written like this:However, because the AS is optional, a subtle problem can occur if you forget the comma between two select_expr expressions: MySQL interprets the second as an alias name. For example, in the following statement, columnb is treated as an alias name:For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases.

• SELECT columna columnb FROM mytable;

• SELECT CONCAT(last_name,', ',first_name) full_name
    FROM mytable ORDER BY full_name;

• SELECT CONCAT(last_name,', ',first_name) AS full_name
    FROM mytable ORDER BY full_name;

---

FROM Clause (FROM 절)

 

    [FROM table_references
        [PARTITION partition_list]]

 

table_references indicates the table or tables from which to retrieve rows. Its syntax is described in Section 13.2.13.2, “JOIN Clause”.

 

SELECT supports explicit partition selection using the PARTITION clause with a list of partitions or subpartitions (or both) following the name of the table in a table_reference (see Section 13.2.13.2, “JOIN Clause”). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, see Section 24.5, “Partition Selection”.

 

The FROM table_references clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 13.2.13.2, “JOIN Clause”. For each table specified, you can optionally specify an alias.

tbl_name [[AS] alias] [index_hint]

The use of index hints provides the optimizer with information about how to choose indexes during query processing. For a description of the syntax for specifying these hints, see Section 8.9.4, “Index Hints”.

---

WHERE Clause (WHERE 절)

 

WHERE where_condition

 

 

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

 

In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (group) functions. See Section 9.5, “Expressions”, and Chapter 12, Functions and Operators.

 

---

GROUP BY Clause (GROUP BY 절)

 

GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]

 

---

HAVING Clause (HAVING 절)

 

HAVING where_condition

 

---

WINDOW Clause (WINDOW 절)

 

WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec) ...]

 

---

ORDER BY Clause (ORDER BY 절)

 

ORDER BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]

 

---

LIMIT Clause (LIMIT 절)

 

LIMIT {[offset,] row_count | row_count OFFSET offset}

 

---

FOR Clause (FOR 절)

 

into_option
    FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
        | LOCK IN SHARE MODE
into_option

 

---

Reference: 한국데이터산업진흥원 저, SQL 전문가 가이드 (2020 개정판), 한국데이터산업진흥원, 2021

 

Reference: MySQL, MySQL 8.0 Reference Manual, 2022년 12월 1일 검색, URL.