측면 조인 사용하기¶
FROM 절에서 LATERAL 구문을 사용하면 인라인 뷰가 앞의 테이블 식에서 열을 참조할 수 있습니다.
예를 들어, 인라인 뷰가 하위 쿼리 인 경우 하위 쿼리는 하위 쿼리 왼쪽에 있는 테이블의 행을 처리할 수 있습니다. 예:
SELECT ...
FROM left_hand_table_expression AS lhte,
LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);
이 동작은 상관 하위 쿼리 와 다소 유사합니다. LATERAL 키워드 뒤의 하위 쿼리는 연관된 하위 쿼리 자체와 유사하며 left_hand_table_expression 은 외부 쿼리와 유사합니다. 래터럴 조인은 상관 관계 하위 쿼리와 달리 여러 행을 반환할 수 있으며, 각 행은 여러 열을 가질 수 있습니다.
다른 유형의 조인은 왼쪽 테이블 식의 행을 처리하기 위해 오른쪽 테이블 식에 직접 전달하지 않습니다.
래터럴 조인의 일반적인 용도는 ARRAY 또는 VARIANT 데이터 타입과 같은 복잡한 데이터 구조를 처리하고 값을 추출하기 위해 FLATTEN 테이블 함수에 대한 호출과 결합하는 것입니다. 예를 보려면 LATERAL 를 참조하십시오.
다른 유형의 조인 출력과 달리, 래터럴 조인의 출력에는 인라인 뷰(하위 쿼리)에서 생성된 행만 포함되며 하위 쿼리의 행이 생성된 후에는 왼쪽 테이블의 모든 행에 교차 조인되지 않습니다.
이 항목의 내용:
용어¶
다음 코드 조각을 고려하십시오.
... FROM te1, LATERAL iv1 ...
래터럴 조인의 왼쪽은 테이블 식(te1)입니다. 래터럴 조인의 오른쪽은 인라인 뷰(iv1)입니다.
테이블 식: 이 항목에서 래터럴 조인의 왼쪽에 있는 테이블 표현식(예: 위의 테이블 표현식
te1)은 테이블로 평가되는 거의 모든 유효한 식이 될 수 있습니다. 예:테이블.
뷰.
하위 쿼리.
테이블 함수의 출력입니다.
이전 조인(래터럴 조인 또는 다른 유형의 조인)의 결과입니다.
인라인 뷰: 이 항목에서는 래터럴 조인의 오른쪽에 있는 식(이 경우
iv1)을 “인라인 뷰”라고 합니다. 이 맥락에서 유효한 인라인 뷰는 다음 중 하나일 수 있습니다.문에 정의된 뷰로, 문의 기간 동안만 유효합니다.
하위 쿼리.
테이블 함수: FLATTEN 과 같은 기본 제공 테이블 함수 또는 사용자 정의 테이블 함수(UDTF).
인라인 뷰는 테이블이 될 수 없습니다.
교차 조인: 이 항목에서 “교차 조인”이라는 용어는 명시적 교차 조인뿐만 아니라 모든 변형(자연 조인, 왼쪽/오른쪽/전체 외부 조인 등)을 포함한 내부 조인 및 외부 조인을 의미합니다.
조인 복습¶
조인은 두 단계로 수행됩니다. 첫째, 서버는 일반적으로 서로 다른 테이블에 있으며 거의 항상 어떤 식으로든 관련되어 있는 두 행을 쌍으로 연결합니다. 둘째, 서버는 쌍의 각 행의 열을 1개의 행으로 조인합니다.
예제 쿼리의 대부분은 아래 표시된 데이터를 사용합니다.
CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
department_ID INTEGER, project_names ARRAY);
INSERT INTO departments (department_ID, name) VALUES
(1, 'Engineering'),
(2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
(101, 'Richards', 1),
(102, 'Paulson', 1),
(103, 'Johnson', 2);
다음은 간단한 내부 조인(래터럴 조인 아님)입니다.
SELECT *
FROM departments AS d, employees AS e
WHERE e.department_ID = d.department_ID
ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
| 1 | Engineering | 101 | Richards | 1 | NULL |
| 1 | Engineering | 102 | Paulson | 1 | NULL |
| 2 | Support | 103 | Johnson | 2 | NULL |
+---------------+-------------+-------------+-----------+---------------+---------------+
보시다시피 행은 일치하는 부서 IDs 에 따라 쌍으로 연결됩니다.
조인은 2개의 대응하는(‘쌍을 이룬’) 입력 행에서 열을 가져와 두 입력 행의 모든 열을 포함하는 1개의 출력 행을 생성합니다. (물론, SELECT 목록을 수정하여 열을 변경할 수 있지만, 가장 간단한 경우 모든 입력 열이 출력에 포함됩니다.)
래터럴 조인은 행을 다르게 쌍으로 연결합니다. 그러나 프로세스의 후반부인 쌍을 이루는 행의 ‘조인’도 비슷합니다. 출력 행은 (거의 항상) 입력 행 쌍의 각 멤버에서 1개 이상의 열을 포함합니다.
래터럴 조인이 행을 쌍으로 연결하는 방법¶
래터럴 조인은 다른 유형의 조인과 다르게 작동합니다. 래터럴 조인은 서버가 다음과 유사한 루프를 실행한 것처럼 작동합니다.
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the current row in the LHT
이 섹션에서는 프로세스의 ‘페어링’ 부분에 초점을 맞추며, 이는 래터럴 조인에서는 다릅니다.
LATERAL 구문을 사용하면 래터럴 조인의 오른쪽에 있는 인라인 뷰에서 뷰 외부에 있는 테이블 식의 열을 참조할 수 있습니다. (아래 예에서 “인라인 뷰”는 실제로 하위 쿼리입니다.)
SELECT *
FROM departments AS d,
LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
| 1 | Engineering | 101 | Richards | 1 | NULL |
| 1 | Engineering | 102 | Paulson | 1 | NULL |
| 2 | Support | 103 | Johnson | 2 | NULL |
+---------------+-------------+-------------+-----------+---------------+---------------+
이 예에서 오른쪽 하위 쿼리의 WHERE 절은 왼쪽 테이블의 값을 참조합니다.
래터럴 조인과 교차 조인의 차이점은 단순히 열에 대한 액세스보다 훨씬 더 크다는 점입니다. 다음 몇 단락에서는 기존의 교차 조인부터 시작하여 이 두 가지 유형의 조인을 대조적으로 설명합니다.
교차 조인은 왼쪽에 있는 테이블의 각 행을 오른쪽에 있는 테이블의 각 행과 결합합니다. 결과는 데카르트 곱입니다.
개념적으로 교차 조인은 아래의 의사 코드에서와 같이 중첩 루프와 유사합니다.
for each row in left_hand_table LHT:
for each row in right_hand_table RHT:
concatenate the columns of the RHT to the columns of the LHT
왼쪽 테이블에 n 개 행이 있고 오른쪽 테이블에 m* 개 행이 있는 경우 교차 조인의 결과는 n x m 행이 됩니다. 예를 들어, 왼쪽 테이블에 1000개의 행이 있고 오른쪽 테이블에 100개의 행이 있는 경우 내부 조인의 결과는 100,000개의 행이 됩니다. 중첩 루프에서 예상할 수 있는 것처럼 외부 루프가 1000회 실행되고 내부 루프가 100회 실행되는 경우 , 외부 루프가 반복될 때마다, 가장 안쪽 문은 100,000회 실행됩니다. (물론, SQL 프로그래머는 FROM 절이나 WHERE 절에 조인 조건 없이 순수한 교차 조인을 작성하는 경우는 거의 없습니다.)
래터럴 조인 쌍은 매우 다르게 기록됩니다. 다음은 래터럴 조인을 구현하기 위한 의사 코드입니다.
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the LHT row,
and concatenate LHT columns to RHS columns
래터럴 조인에는 출력을 변경하는 2개의 중첩 루프가 아닌 하나의 루프만 있습니다.
교차 조인의 경우 출력은 100,000행이었습니다. 왼쪽에 동일한 1,000개 행 테이블이 있고 입력 행당 하나의 출력 행을 출력하는 오른쪽 인라인 뷰(예: 하위 쿼리)를 사용하는 래터럴 조인의 경우 래터럴 조인의 출력은 100,000개 행이 아니라 1,000개 행이 됩니다.
래터럴 조인은 왼쪽 테이블의 각 입력 행에 대해 오른쪽의 인라인 뷰는 0개 이상의 행을 생성하는 것으로 생각할 수 있습니다. 그런 다음 하위 쿼리의 각 출력 행이 입력 행(왼쪽에 있는 전체 테이블이 아님)에 조인되어 하위 쿼리에서 선택한 열과 LHT 입력 행의 열을 포함하는 행이 생성됩니다.
래터럴 조인의 오른쪽에 있는 인라인 뷰는 각 입력 행에 대해 정확히 1개의 출력 행을 생성하지 않습니다. 1개의 입력 행에 대해 오른쪽의 출력은 0행, 1행 또는 여러 행이 될 수 있습니다. 이러한 각 출력 행은 원래 입력 행의 열에 조인됩니다.
하위 쿼리가 각 입력 행에 대해 정확히 하나의 출력 행을 생성하지 않는 경우 래터럴 조인이 반드시 왼쪽 테이블에 있는 행 수만큼 정확히 많은 행을 생성하지 않습니다. 왼쪽 테이블에 1,000개 행이 있고 인라인 뷰가 각 입력 행에 대해 2개의 출력 행을 생성하는 경우 래터럴 조인의 결과는 2,000개 행입니다.
지금까지의 각 래터럴 조인 예제에서는 외부 쿼리에 ON 절이나 WHERE 절이 없어서 레코드를 쌍으로 연결하지 못했습니다. 페어링(있는 경우)은 인라인 뷰로 전달된 개별 행을 기반으로 인라인 뷰에서 수행됩니다. 인라인 뷰가 WHERE 절이 있는 하위 쿼리인 경우 이는 상당히 명확합니다. 오른쪽 식이 하위 쿼리가 아닌 테이블 함수인 경우와 같이 다른 경우에는 반드시 명확하지 않습니다. (이후 예제에서는 하위 쿼리 대신 FLATTEN 테이블 함수를 사용하는 오른쪽 식을 보여 줍니다.)
상관 관계 하위 쿼리 또는 테이블 함수의 조인에 능숙한 사용자는 다음과 같은 비교를 통해 래터럴 조인이 교차 조인과 어떻게 다른지 이해하는 데 도움이 될 수 있습니다. 상관 관계 하위 쿼리 또는 조인 테이블 함수에 익숙하지 않은 독자는 이 섹션을 건너뛸 수 있습니다.
테이블 함수 조인과 래터럴 조인의 유사점¶
래터럴 조인은 테이블과 사용자 정의 테이블 함수(UDTF) 간의 “조인”과 유사합니다. 예를 들어, 다음 SQL 문을 생각해 보겠습니다.
SELECT *
FROM t1, TABLE(udtf2(t1.col1))
...
;
테이블과 UDTF 간의 조인을 구현하기 위한 의사 코드는 다음과 같습니다.
for each row in left_hand_table LHT:
udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.
이는 기본적으로 래터럴 조인을 구현하는 코드와 동일합니다.
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the LHT row
예: FLATTEN 테이블 함수와 함께 래터럴 조인 사용¶
래터럴 조인은 기본 제공되는 FLATTEN 테이블 함수와 함께 사용되는 경우가 많습니다. FLATTEN 함수는 여러 값을 저장할 수 있는 데이터 타입(예: ARRAY, VARIANT, OBJECT)과 함께 사용되는 경우가 많습니다. 예를 들어, 배열에는 일반적으로 여러 값이 포함됩니다. 마찬가지로, VARIANT 열에는 JSON 데이터 값이 포함될 수 있으며, 여기에는 사전(해시) 또는 목록이 포함될 수 있습니다. (그리고 여기에는 다른 값이 포함될 수 있습니다.)
ARRAY 값은 다음과 같이 생성할 수 있습니다.
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs')
WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
WHERE employee_ID = 102;
FLATTEN 함수는 해당 값 내부에서 값을 추출할 수 있습니다. 이 함수는 VARIANT, OBJECT 또는 ARRAY 타입의 단일 식을 취하고 해당 식의 값을 행 세트(0개 이상의 행, 각 행에 1개 이상의 열이 포함됨)로 추출합니다. 이 행 세트는 뷰 또는 테이블에 해당합니다. 이 뷰는 정의된 문의 기간 동안만 존재하므로 일반적으로 “인라인 뷰”라고 합니다.
다음 예제에서는 FLATTEN 을 사용하여 배열에서 값을 추출합니다(래터럴 조인 미사용):
SELECT index, value AS project_name
FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));
+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
| 0 | "project1" |
| 1 | "project2" |
+-------+--------------+
FLATTEN 에서 생성된 인라인 뷰는 LATERAL 키워드와 함께 사용할 수 있지만 반드시 사용해야 하는 것은 아닙니다. 예:
SELECT * FROM table1, LATERAL FLATTEN(...);
LATERAL 키워드와 함께 사용하는 경우 인라인 뷰는 그 앞에 있는 테이블의 열에 대한 참조를 포함할 수 있습니다.
SELECT emp.employee_ID, emp.last_name, index, value AS project_name
FROM employees AS emp,
LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
ORDER BY employee_ID;
+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME |
|-------------+-----------+-------+----------------------|
| 101 | Richards | 0 | "Materialized Views" |
| 101 | Richards | 1 | "UDFs" |
| 102 | Paulson | 0 | "Materialized Views" |
| 102 | Paulson | 1 | "Lateral Joins" |
+-------------+-----------+-------+----------------------+