加入
適用于: Databricks SQL Databricks Runtime
根據聯結準則結合兩 個數據表參考 的資料列。
語法
left_table_reference { [ join_type ] JOIN right_table_reference join_criteria |
NATURAL join_type JOIN right_table_reference |
CROSS JOIN right_table_reference }
join_type
{ [ INNER ] |
LEFT [ OUTER ] |
[ LEFT ] SEMI |
RIGHT [ OUTER ] |
FULL [ OUTER ] |
[ LEFT ] ANTI |
CROSS }
join_criteria
{ ON boolean_expression |
USING ( column_name [, ...] ) }
參數
-
聯結左側的資料表參考。
-
聯結右側的資料表參考。
join_type
聯結類型。
[ INNER ]
傳回兩個數據表參考中具有相符值的資料列。 預設聯結類型。
LEFT [ OUTER ]
傳回左資料表參考的所有值,以及右邊資料表參考的相符值,如果沒有相符值,則傳回 。
NULL
它也稱為 左外部聯結。RIGHT [ OUTER ]
傳回右資料表參考的所有值,以及來自左表參考的相符值,如果沒有相符值,則會附加
NULL
。 它也稱為 右外部聯結。FULL [OUTER]
傳回這兩個關聯的所有值,並將值附加
NULL
在不相符的端。 它也稱為 完整外部聯結。[ LEFT ]半
傳回資料表參考左邊的值,其與右邊相符。 也稱為 左半聯結。
[ LEFT ]反
從左資料表參考傳回與右資料表參考不相符的值。 它也稱為 左反聯結。
CROSS JOIN
傳回兩個關聯性之笛卡兒乘積。
自然
指定兩個關聯中的資料列會隱含地比對具有相符名稱的所有資料行是否相等。
join_criteria
指定一個資料表參考中的資料列如何與另一個資料表參考的資料列結合。
ON boolean_expression
具有 BOOLEAN 之傳回類型的運算式,指定兩個關聯資料列的比對方式。 如果結果為 true,則會將資料列視為相符專案。
USING ( column_name [, ...] )
比較兩個關聯中必須存在之資料行清單的
column_name
相等,以比對資料列。USING (c1, c2)
是 的ON rel1.c1 = rel2.c1 AND rel1.c2 = rel2.c2
同義字。
-
具有選擇性資料行識別碼清單的暫存名稱。
注意
當您指定 USING
或 NATURAL
時, SELECT *
每個用來比對的資料行只會顯示一個出現專案。
如果您省略 join_criteria
任何 join_type
的語意,就會變成 的 CROSS JOIN
語意。
例子
-- Use employee and department tables to demonstrate different type of joins.
> CREATE TEMP VIEW employee(id, name, deptno) AS
VALUES(105, 'Chloe', 5),
(103, 'Paul' , 3),
(101, 'John' , 1),
(102, 'Lisa' , 2),
(104, 'Evan' , 4),
(106, 'Amy' , 6);
> CREATE TEMP VIEW department(deptno, deptname) AS
VALUES(3, 'Engineering'),
(2, 'Sales' ),
(1, 'Marketing' );
-- Use employee and department tables to demonstrate inner join.
> SELECT id, name, employee.deptno, deptname
FROM employee
INNER JOIN department ON employee.deptno = department.deptno;
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
-- Use employee and department tables to demonstrate left join.
> SELECT id, name, employee.deptno, deptname
FROM employee
LEFT JOIN department ON employee.deptno = department.deptno;
105 Chloe 5 NULL
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
104 Evan 4 NULL
106 Amy 6 NULL
-- Use employee and department tables to demonstrate right join.
> SELECT id, name, employee.deptno, deptname
FROM employee
RIGHT JOIN department ON employee.deptno = department.deptno;
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
-- Use employee and department tables to demonstrate full join.
> SELECT id, name, employee.deptno, deptname
FROM employee
FULL JOIN department ON employee.deptno = department.deptno;
101 John 1 Marketing
106 Amy 6 NULL
103 Paul 3 Engineering
105 Chloe 5 NULL
104 Evan 4 NULL
102 Lisa 2 Sales
-- Use employee and department tables to demonstrate cross join.
> SELECT id, name, employee.deptno, deptname
FROM employee
CROSS JOIN department;
105 Chloe 5 Engineering
105 Chloe 5 Marketing
105 Chloe 5 Sales
103 Paul 3 Engineering
103 Paul 3 Marketing
103 Paul 3 Sales
101 John 1 Engineering
101 John 1 Marketing
101 John 1 Sales
102 Lisa 2 Engineering
102 Lisa 2 Marketing
102 Lisa 2 Sales
104 Evan 4 Engineering
104 Evan 4 Marketing
104 Evan 4 Sales
106 Amy 4 Engineering
106 Amy 4 Marketing
106 Amy 4 Sales
-- Use employee and department tables to demonstrate semi join.
> SELECT *
FROM employee
SEMI JOIN department ON employee.deptno = department.deptno;
103 Paul 3
101 John 1
102 Lisa 2
-- Use employee and department tables to demonstrate anti join.
> SELECT *
FROM employee
ANTI JOIN department ON employee.deptno = department.deptno;
105 Chloe 5
104 Evan 4
106 Amy 6
-- Use employee and department tables to demonstrate lateral inner join.
> SELECT id, name, deptno, deptname
FROM employee
JOIN LATERAL (SELECT deptname
FROM department
WHERE employee.deptno = department.deptno);
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
-- Use employee and department tables to demonstrate lateral left join.
> SELECT id, name, deptno, deptname
FROM employee
LEFT JOIN LATERAL (SELECT deptname
FROM department
WHERE employee.deptno = department.deptno);
105 Chloe 5 NULL
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
104 Evan 4 NULL
106 Amy 6 NULL