1.from A inner join B on A.ID=B.ID :兩表都有的記錄才列出
A表： ID Name B表： ID Class
1 BMW 1 C1
2 Aodi 2 C2
Null Benz 4 Null
3 Null Null C4
select * from A inner join B on A.ID=B.ID:
ID Name ID Class
1 BMW 1 C1
2 Aodi 2 C2
SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1 2.from A left join B on A.ID=B.ID:
A表中所有記錄列出，B中無法比對的用Null比對
select * from A left join B on A.ID=B.ID:
1 BMW 1 C1
2 Aodi 2 C2
Null Benz Null Null
3 Null Null Null
SELECT * FROM Tables1 t1 LEFT OUTER JOIN Table2 t2 on t1.Col1 = t2.Col2 SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 WHERE t2.Col1 IS NULL 3.from A right join B on A.ID=B.ID:
B表中所有記錄列出,A中無法比對的用Null比對
select * from A right join B on A.ID=B.ID:
ID Name ID Class
1 BMW 1 C1
2 Aodi 2 C2
Null Null 4 Null
Null Null Null C4
SELECT * FROM Tables1 t1 RIGHT OUTER JOIN Table2 t2 on t1.Col1 = t2.Col2 SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 WHERE t2.Col1 IS NULL 4.from A full outer join B on A.ID=B.ID.
列出A表所有記錄，B表所有記錄。無法比對的用Null替代
select * from A full outer join B on A.ID=B.ID.
ID Name ID Class
1 BMW 1 C1
2 Aodi 2 C2
Null Benz Null Null
3 Null Null Null
Null Null 4 Null
Null Null Null C4
SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL OR t2.ID IS NULL CROSS JOIN：
交叉連接配接不需要任何連接配接條件。這個會把兩個表的的資料進行笛卡爾積操作。
SELECT * FROM Table1 t1 CROSS JOIN Table2 t2