SELECT

설명

Select 구문의 사용법을 주로 소개합니다

문법:

SELECT
    [hint_statement, ...]
    [ALL | DISTINCT | DISTINCTROW | ALL EXCEPT ( col_name1 [, col_name2, col_name3, ...] )]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
      [TABLET tabletid_list]
      [TABLESAMPLE sample_value [ROWS | PERCENT]
        [REPEATABLE pos_seek]]
    [WHERE where_condition]
    [GROUP BY [GROUPING SETS | ROLLUP | CUBE] {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name']

1. 구문 설명:

   1. select_expr, ... 조회하여 결과에 표시할 열입니다. 별칭을 사용할 때 as는 생략할 수 있습니다.

   2. select_expr, ... 조회 대상 테이블입니다. 하나 이상의 테이블을 지정할 수 있으며, 서브쿼리로 생성된 임시 테이블도 포함됩니다.

   3. where_definition은 조회 조건(표현식)입니다. WHERE 절이 있으면 이 조건으로 행 데이터를 필터링합니다. where_condition은 선택할 각 행에 대해 true로 평가되는 표현식입니다. WHERE 절이 없으면 모든 행을 선택합니다. WHERE 표현식에서는 집계 함수를 제외하고 MySQL이 지원하는 모든 함수와 연산자를 사용할 수 있습니다.

   4. ALL | DISTINCT : 결과 집합의 중복 제거 여부를 지정합니다. all은 모든 결과를 반환하고, distinct/distinctrow는 중복 열을 제거합니다. 기본값은 all입니다.

   5. ALL EXCEPT: 전체(all) 결과 집합을 필터링합니다. except는 전체 결과 집합에서 제외할 하나 이상의 열 이름을 지정합니다. 일치하는 모든 열 이름은 출력에서 제외됩니다.

   이 기능은 Apache Doris 1.2 버전부터 지원됩니다.

   6. INTO OUTFILE 'file_name' : 결과를 이전에 존재하지 않았던 새 파일에 저장합니다. 저장 형식에 따라 차이가 있습니다.

   7. Group by having: 결과 집합을 그룹화하고, having이 있으면 group by의 결과를 필터링합니다. Grouping Sets, Rollup, Cube는 group by의 확장 기능입니다. 자세한 내용은 GROUPING SETS 설계를 참고하세요.

   8. Order by: 최종 결과를 정렬합니다. Order by는 하나 이상의 열 값을 비교하여 결과 집합을 정렬합니다.

      Order by는 정렬하기 전에 모든 데이터를 1개 노드로 보내야 하므로 시간과 리소스를 많이 소모하는 작업입니다. 또한 정렬 작업은 정렬하지 않는 작업보다 더 많은 메모리를 필요로 합니다.

      정렬된 결과에서 상위 N개를 반환하려면 LIMIT 절을 사용해야 합니다. 메모리 사용량을 제한하기 위해 사용자가 LIMIT 절을 지정하지 않으면 기본적으로 정렬된 결과의 처음 65535개를 반환합니다.

   9. Limit n: 출력 결과의 행 수를 제한합니다. limit m,n은 m번째 행부터 n개의 레코드를 출력한다는 뜻입니다. order by를 사용한 다음 limit m,n을 사용해야 합니다. 그렇지 않으면 실행할 때마다 데이터가 달라질 수 있습니다.

   10. Having 절은 테이블의 행 데이터를 필터링하는 것이 아니라 집계 함수가 생성한 결과를 필터링합니다.

       일반적으로 having은 집계 함수(예: COUNT(), SUM(), AVG(), MIN(), MAX()) 및 group by 절과 함께 사용합니다.

   11. SELECT는 파티션이나 하위 파티션(또는 둘 다)의 목록을 포함하는 PARTITION을 사용하여 파티션을 명시적으로 선택할 수 있습니다. PARTITION은 table_reference의 테이블 이름 뒤에 지정합니다.

   12. [TABLET tids] TABLESAMPLE n [ROWS | PERCENT] [REPEATABLE seek]: FROM 절의 테이블에서 읽을 행 수를 제한합니다. 지정된 행 수 또는 백분율에 따라 테이블에서 여러 Tablet을 의사 난수 방식으로 선택하고, REPEATABLE에 시드 값을 지정하여 선택한 샘플을 다시 반환할 수 있습니다. TableID를 직접 지정할 수도 있습니다. 이 기능은 OLAP 테이블에서만 사용할 수 있습니다.

   13. hint_statement: selectlist 앞에 지정하는 힌트입니다. 힌트를 사용하면 옵티마이저의 동작에 영향을 주어 원하는 실행 계획을 얻을 수 있습니다. 자세한 내용은 다음 문서를 참고하세요: [joinHint 사용 문서] (https://doris.apache.org/docs/dev/query-acceleration/hints/hints-overview)

구문 제약 사항:

1. SELECT는 테이블을 참조하지 않고 계산된 행을 조회하는 데에도 사용할 수 있습니다.
2. 모든 절은 위 형식의 순서를 엄격히 따라야 합니다. HAVING 절은 GROUP BY 절 뒤, ORDER BY 절 앞에 배치해야 합니다.
3. 별칭 키워드 AS는 생략할 수 있습니다. 별칭은 group by, order by 및 having에서 사용할 수 있습니다.
4. Where 절: WHERE 문은 GROUP BY에 포함할 행을 결정하고, HAVING은 결과 집합에서 사용할 행을 결정합니다.
5. HAVING 절은 count, sum, max, min, avg와 같은 집계 함수를 참조할 수 있지만 WHERE 절은 참조할 수 없습니다. WHERE 절은 집계 함수를 제외한 다른 함수를 참조할 수 있습니다. Where 절에서 조건을 정의할 때는 열 별칭을 사용할 수 없습니다.
6. Group by 뒤에 with rollup을 지정하면 결과를 한 번 이상 추가로 집계할 수 있습니다.

조인 쿼리:

Doris는 JOIN 구문을 지원합니다.

JOIN
table_references:
    table_reference [, table_reference] …
table_reference:
    table_factor
  | join_table
table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
join_condition:
    ON conditional_expr

UNION 문법:

SELECT ...
UNION [ALL| DISTINCT] SELECT ......
[UNION [ALL| DISTINCT] SELECT ...]

UNION은 여러 SELECT 문의 결과를 하나의 결과 집합으로 결합하는 데 사용합니다.

첫 번째 SELECT 문의 열 이름이 반환 결과의 열 이름으로 사용됩니다. 각 SELECT 문에서 같은 위치에 나열된 열은 데이터 타입이 같아야 합니다. 예를 들어 첫 번째 문에서 선택한 첫 번째 열은 다른 문에서 선택한 첫 번째 열과 타입이 같아야 합니다.

UNION은 기본적으로 결과에서 중복 행을 제거합니다. 선택적으로 지정할 수 있는 DISTINCT 키워드도 중복 행 제거를 지정하므로 기본 동작과 차이가 없습니다. 선택적 키워드인 ALL을 사용하면 중복 행을 제거하지 않으며, 모든 SELECT 문에서 조건에 일치하는 모든 행이 결과에 포함됩니다.

WITH 문:

공통 테이블 표현식을 지정하려면 쉼표로 구분된 하나 이상의 하위 절을 포함하는 WITH 절을 사용합니다. 각 하위 절은 결과 집합을 생성하는 서브쿼리를 제공하고 이름을 해당 서브쿼리와 연결합니다. 다음 예에서는 WITH 절에 cte1과 cte2라는 CTE를 정의하고, 이 WITH 절 아래에 있는 최상위 SELECT에서 이를 참조합니다.

WITH
  cte1 AS (SELECT a，b FROM table1),
  cte2 AS (SELECT c，d FROM table2)
SELECT b，d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

WITH 절이 포함된 문에서는 각 CTE 이름을 참조하여 해당 CTE의 결과 집합에 접근할 수 있습니다.

다른 CTE에서 CTE 이름을 참조할 수 있으므로, 다른 CTE를 기반으로 CTE를 정의할 수 있습니다.

재귀 CTE는 현재 지원되지 않습니다.

예제

아래 예제 1-9는 다음 두 테이블을 대상으로 실행됩니다. 나머지 예제(UNION, WITH, JOIN, TABLESAMPLE)는 이 페이지에서 생성하지 않은 테이블(t1, t2, employee, left_tbl, tournament, ...)을 참조합니다. 해당 코드는 구문의 형태를 보여 주기 위한 예시이므로 실행할 때는 자신의 테이블로 바꾸세요.

CREATE TABLE student (Name VARCHAR(32), age INT)
DISTRIBUTED BY HASH(Name) BUCKETS 1
PROPERTIES ("replication_num" = "1");

INSERT INTO student VALUES
  ('Alice', 18), ('Bob', 20), ('Carol', 22), ('Dave', 25), ('Eve', 30);

CREATE TABLE tb_book (id INT, name VARCHAR(64), price DECIMAL(10,2), type VARCHAR(32))
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES ("replication_num" = "1");

INSERT INTO tb_book VALUES
  (1, 'SQL Cookbook',                          39.99, 'database'),
  (2, 'Doris Internals',                       49.99, 'database'),
  (3, 'The Rust Programming Language',         45.00, 'programming'),
  (4, 'Designing Data-Intensive Applications', 55.00, 'database'),
  (5, 'Clean Code',                            35.00, 'programming');

1. 나이가 18, 20, 25인 학생의 이름 조회

   select Name from student where age in (18,20,25);

2. ALL EXCEPT 예제

   -- Query all information except the students' age
   select * except(age) from student; 

3. GROUP BY 예제

   --Query the tb_book table, group by type, and find the average price of each type of book,
   select type,avg(price) from tb_book group by type;

4. DISTINCT 사용

   --Query the tb_book table to remove duplicate type data
   select distinct type from tb_book;

5. ORDER BY 예제

   쿼리 결과를 오름차순(기본값) 또는 내림차순(DESC)으로 정렬합니다. 오름차순에서는 NULL이 처음에, 내림차순에서는 NULL이 마지막에 배치됩니다.

   --Query all records in the tb_book table, sort them in descending order by id, and display three records
   select * from tb_book order by id desc limit 3;

6. LIKE 패턴 매칭 쿼리

   패턴 매칭 쿼리를 수행할 수 있습니다. 와일드카드는 %와 _ 두 가지이며, %는 하나 이상의 문자와 일치하고 _는 한 문자와 일치합니다.

   --Find all books whose second character is h
   select * from tb_book where name like('_h%');

7. LIMIT으로 결과 행 수 제한

   --1. Display 3 records in descending order
   select * from tb_book order by price desc limit 3;
   
   --2. Display 4 records from id=1
   select * from tb_book where id limit 1,4;

8. CONCAT으로 여러 열 결합

   --Combine name and price into a new string output
   select id,concat(name,":",price) as info,type from tb_book;

9. 함수와 표현식 사용

   --Calculate the total price of various books in the tb_book table
   select sum(price) as total,type from tb_book group by type;
   --20% off price
   select *,(price * 0.8) as "20%" from tb_book;

10. UNION 예제

    SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER BY a LIMIT 10
    UNION
    SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER BY a LIMIT 10;

11. WITH 절 예제

    WITH cte AS
    (
      SELECT 1 AS col1, 2 AS col2
      UNION ALL
      SELECT 3, 4
    )
    SELECT col1, col2 FROM cte;

12. JOIN 예제

    SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                     ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

    다음과 동등합니다.

    SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                     ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

13. INNER JOIN

    SELECT t1.name, t2.salary
      FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
    
    SELECT t1.name, t2.salary
      FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

14. LEFT JOIN

    SELECT left_tbl.*
      FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
      WHERE right_tbl.id IS NULL;

15. RIGHT JOIN

    mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
    +------+------+------+------+
    | a    | b    | a    | c    |
    +------+------+------+------+
    |    2 | y    |    2 | z    |
    | NULL | NULL |    3 | w    |
    +------+------+------+------+

16. TABLESAMPLE

    --Pseudo-randomly sample 1000 rows in t1. Note that several Tablets are actually selected according to the statistics of the table, and the total number of selected Tablet rows may be greater than 1000, so if you want to explicitly return 1000 rows, you need to add Limit.
    SELECT * FROM t1 TABLET(10001) TABLESAMPLE(1000 ROWS) REPEATABLE 2 limit 1000;

키워드

SELECT

모범 사례

1. SELECT 절에 관한 추가 정보

   • select_expr에 AS alias_name을 사용하여 별칭을 지정할 수 있습니다. 별칭은 표현식에서 열 이름으로 사용되며 GROUP BY, ORDER BY 또는 HAVING 절에서 사용할 수 있습니다. 열 별칭을 지정할 때는 AS 키워드를 사용하는 것이 좋습니다.

   • FROM 뒤의 table_references는 쿼리에 참여하는 하나 이상의 테이블을 나타냅니다. 둘 이상의 테이블을 나열하면 JOIN 연산을 수행합니다. 지정한 각 테이블에 별칭을 정의할 수도 있습니다.

   • SELECT 뒤에 오는 선택 열은 ORDER IN 및 GROUP BY에서 열 이름, 열 별칭 또는 열 위치를 나타내는 정수(1부터 시작)로 참조할 수 있습니다.

     SELECT college, region, seed FROM tournament
       ORDER BY region, seed;
     
     SELECT college, region AS r, seed AS s FROM tournament
       ORDER BY r, s;
     
     SELECT college, region, seed FROM tournament
       ORDER BY 2, 3;

   • 서브쿼리에 ORDER BY가 있고 외부 쿼리에도 ORDER BY가 적용되면 가장 바깥쪽의 ORDER BY가 우선합니다.

   • GROUP BY를 사용하면 그룹화된 열이 자동으로 오름차순 정렬됩니다. 이는 동일한 열을 지정한 ORDER BY 문이 있는 것과 같습니다. 자동 정렬로 인한 GROUP BY의 오버헤드를 피하려면 ORDER BY NULL을 추가하면 됩니다.

     SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

   • SELECT에서 ORDER BY 또는 GROUP BY로 열을 정렬할 때 서버는 max_sort_length 시스템 변수가 지정하는 바이트 수만큼의 앞부분만 사용하여 값을 정렬합니다.

   • HAVING 절은 일반적으로 결과 집합이 MySQL 클라이언트에 반환되기 직전에 마지막으로 적용되며 최적화되지 않습니다. (LIMIT은 HAVING 다음에 적용됩니다.)

     SQL 표준에 따르면 HAVING은 GROUP BY 목록에 있거나 집계 함수에서 사용하는 열을 참조해야 합니다. 하지만 MySQL은 이를 확장하여 HAVING에서 Select 절 목록의 열과 외부 서브쿼리의 열도 참조할 수 있도록 허용합니다.

     HAVING이 참조하는 열이 모호하면 경고가 발생합니다. 다음 문에서 col2는 모호합니다.

     SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

   • WHERE를 사용해야 할 곳에 HAVING을 사용하지 않도록 주의하세요. HAVING은 GROUP BY와 함께 사용합니다.

   • HAVING 절은 집계 함수를 참조할 수 있지만 WHERE는 참조할 수 없습니다.

     SELECT user, MAX(salary) FROM users
       GROUP BY user HAVING MAX(salary) > 10;

   • LIMIT 절은 SELECT 문이 반환하는 행 수를 제한하는 데 사용할 수 있습니다. LIMIT은 하나 또는 두 개의 인수를 받을 수 있으며, 모두 음이 아닌 정수여야 합니다.

     /*Retrieve 6~15 rows in the result set*/
     SELECT * FROM tbl LIMIT 5,10;
     /*Then if you want to retrieve all rows after a certain offset is set, you can set a very large constant for the second parameter. The following query fetches all data from row 96 onwards */
     SELECT * FROM tbl LIMIT 95,18446744073709551615;
     /*If LIMIT has only one parameter, the parameter specifies the number of rows that should be retrieved, and the offset defaults to 0, that is, starting from the first row*/

   • SELECT...INTO를 사용하면 쿼리 결과를 파일에 기록할 수 있습니다.

2. SELECT 키워드의 한정자

   • 중복 제거

     ALL과 DISTINCT 한정자는 결과 집합에서 행의 중복을 제거할지 지정합니다. 열에 적용되는 것은 아닙니다.

     ALL은 기본 한정자이며, 조건을 충족하는 모든 행을 조회합니다.

     DISTINCT는 중복 행을 제거합니다.

3. 서브쿼리의 주요 장점

   • 서브쿼리를 사용하면 쿼리를 구조화하여 문의 각 부분을 분리할 수 있습니다.
   • 일부 연산에는 복잡한 합집합과 조인이 필요합니다. 서브쿼리는 이러한 연산을 수행하는 또 다른 방법을 제공합니다.

4. 쿼리 속도 향상

   • 데이터 스캔 범위를 줄이기 위해 Doris의 파티션과 버킷을 데이터 필터링 조건으로 최대한 활용합니다.
   • 쿼리 속도를 높이기 위해 Doris의 접두사 인덱스 필드를 데이터 필터링 조건으로 충분히 활용합니다.

5. UNION

   • union 키워드만 사용하는 것은 union disitnct를 사용하는 것과 효과가 같습니다. 중복 제거 작업은 메모리를 더 많이 사용하므로 union all 연산을 사용하면 쿼리 속도가 더 빠르고 메모리 사용량도 줄어듭니다. 반환되는 결과 집합에 order by와 limit 연산을 적용하려면 union 연산을 서브쿼리에 넣고, 해당 서브쿼리에서 select로 조회한 다음, 서브쿼리와 order by를 외부 쿼리에 배치해야 합니다.

     select * from (select age from student_01 union all select age from student_02) as t1
     order by age limit 4;
       
     +-------------+
     | age |
     +-------------+
     | 18 |
     | 19 |
     | 20 |
     | 21 |
     +-------------+
     4 rows in set (0.01 sec)

6. JOIN

   • 내부 조인 조건은 등가 조인뿐 아니라 비등가 조인도 지원합니다. 성능을 위해 등가 조인을 사용하는 것이 좋습니다.
   • 다른 조인은 등가 조인만 지원합니다.