合并多个表JOIN ON
JOIN = INNER JOIN
根据某列合并两个表,只合并两个表中在该列的共有值,不共有的值忽略
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
LEFT JOIN
如果按某列合并的表中,两个表的这列中的值不完全相同,则按照第一列中的所有值合并,忽略第二项中的特有值
SELECT *
FROM table1
LEFT JOIN table2
ON table1.c2 = table2.c2;
CROSS JOIN
结合两表指定列中的所有值
SELECT shirts.shirt_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;
UNION
把两个表叠在一起
SELECT *
FROM table1
UNION
SELECT *
FROM table2;
两个表必须有相同的列和类型
WITH语句
可以把查询结果作为一个临时表,再用来联合其他表完成查询
WITH previous_results AS (
SELECT ...
...
...
...
)
SELECT *
FROM previous_results
JOIN customers
ON _____ = _____;