1. INNER JOIN 内连接
语法:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
注意:INNER JOIN 与 JOIN 是相同的。
图示:
2. LEFT JOIN 左连接
语法:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
注释:在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN。
图示:
3. RIGHT JOIN 右链接
语法:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
注释:在某些数据库中,RIGHT JOIN 称为 RIGHT OUTER JOIN。
图示:
4. FULL OUTER JOIN 全连接
语法:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
注意:MySQL中不支持 FULL OUTER JOIN,你可以在 SQL Server 测试以下实例。
图示: