[mysql] with 절(가상테이블) 02

https://dev.mysql.com/doc/refman/8.0/en/with.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.20 WITH (Common Table Expressions)

 

 

 

 

 

13.2.20 WITH (Common Table Expressions)

 

 

Common Table Expressions

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 안에 하나 이상의 쿼리 절을 사용함

cte1, cte2 테이블을 정의함

 

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

 

 

 

 

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

nonstrict SQL mode, the statement produces this output

 

UNION 은 붙인다는 뜻이다

컬럼 n, str 을 붙임

 

 

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

 

 

 

# UNION 결과 : 1,2 출력

SELECT 1 AS num
UNION
SELECT 2 AS num;Copy

 

# WITH, UNION 결과 : 1,2 출력

WITH test AS
(
  SELECT 1 AS num
  UNION ALL
  SELECT 2 AS num
)

SELECT * FROM test;Copy

 

# WITH, UNION 결과 : 1,2,2,2,... (emp테이블의 행수만큼 2가 반복됨)

WITH test AS
(
  SELECT 1 AS num
  UNION ALL
  SELECT 2 AS num FROM emp # 이 문장은 emp 테이블의 행수만큼 반복된다
)

SELECT * FROM test;