1、内连接 A∩B
SELECT *
FROM table_name1 a JOIN table_name2 b
ON a.id = b.id;
2、左外连接
SELECT *
FROM table_name1 a LEFT JOIN table_name2 b
ON a.id = b.id;
3、右外连接
SELECT *
FROM table_name1 a RIGHT JOIN table_name2 b
ON a.id = b.id;
4、A - A∩B
SELECT *
FROM table_name1 a LEFT JOIN table_name2 b
ON a.id = b.id
WHERE b.id IS NULL;
5、B - A∩B
SELECT *
FROM table_name1 a RIGHT JOIN table_name2 b
ON a.id = b.id
WHERE b.id IS NULL;
6、( A - A∩B ) + B
SELECT *
FROM table_name1 a LEFT JOIN table_name2 b
ON a.id = b.id
WHERE b.id IS NULL
UNION ALL #没有去重操作,效率高
SELECT *
FROM table_name1 a RIGHT JOIN table_name2 b
ON a.id = b.id;
7、 ( A - A∩B ) + ( B - A∩B )
SELECT *
FROM table_name1 a LEFT JOIN table_name2 b
ON a.id = b.id
WHERE b.id IS NULL
UNION ALL
SELECT *
FROM table_name1 a RIGHT JOIN table_name2 b
ON a.id = b.id
WHERE a.id IS NULL;