内连接 INNER JOIN
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
{
SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id-- 也可 USING(customer_id)
---
SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
}
跨数据库连接
{
USE sql_store;
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id-- 跨数据库连接注意sql_inventory.
}
自连接
{
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to=m.employee_id-- 注意这里对employees表命了两个别名
}
多表连接
{
USE sql_store;
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name AS status
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id
JOIN order_statuses os
ON o.status=os.order_status_id
}
复合连接
{
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id=oin.order_id
AND oi.product_id=oin.product_id-- 多条件情况下的连接
}
隐式连接
{
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id
---
SELECT *
FROM orders o,customers c
WHERE o.customer_id=c.customer_id
-- 后者为隐式连接，两者等价，但是隐式连接中，如果忘记了WHERE，其不会报错，但是会输出交差连接。
}
外连接 OUTER JOIN（LEFT/RIGHT JOIN）
{
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id=o.customer_id
ORDER BY c.customer_id
-- 注意左连接和右连接中的左和右是相对位置。LEFT JOIN中，相对位置上左表为customers，连接右表orders
-- 左向外联接的结果集包括 LEFT OUTER JOIN子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值NULL。
---
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM orders o
RIGHT JOIN customers c
ON c.customer_id=o.customer_id
ORDER BY c.customer_id
-- 两者返回结果一样，尽量使用左连接，防止左右连接杂糅程序难读
}
多表外连接
{
SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name AS shipper
FROM customers c
LEFT JOIN orders o
ON c.customer_id=o.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
---
SELECT
o.order_date,
o.order_id,
c.first_name,
sh.name AS shipper,
os.name AS status
FROM orders o
JOIN customers c
ON c.customer_id=o.customer_id
LEFT JOIN shippers sh
ON sh.shipper_id=o.shipper_id
JOIN order_statuses os
ON o.status = os.order_status_id
ORDER BY status
}
自外连接
{
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to= m.employee_id -- 注意这里对employees表命了两个别名e m
}
USING
{
SELECT
o.order_id,
c.first_name
FROM orders o
JOIN customers c
USING (customer_id) -- ON o.customer_id=c.customer_id
JOIN shippers sh
USING (shipper_id)
-- USING关键字只能在不同表中列名字完全一致的情况下使用
---
SELECT *
FROM order_items oi
JOIN order_item_notes oin
USING (order_id, product_id)
---
SELECT
p.date,
c.name AS client,
p.amount,
pm.name
FROM payments p
JOIN clients c
USING (client_id)
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
}
自然连接
系统会自己选择连接，可能出现意想不到的结果，不建议使用
{
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
}
交叉连接 CROSS JOIN
一般用来返回连接表的笛卡尔积。交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。
{
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name-- 用于如小中大型号的、红黄蓝颜色的各种商品
}
合并 UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
{
SELECT
order_id,
order_date,
'Actice' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION-- UNION与UNION ALL 都是行合并，前者去重，后者不去重，会全部罗列出来。
SELECT
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'-- 通过UNION合并多个查询结果
---
SELECT name
FROM shippers
UNION
SELECT first_name
FROM customers-- 第一段查询决定了列名，这里列名是name而不会是first_name
}