内连接 —— INNER JOIN

用于连接两个表的数据，通过ON关键字+条件语句来过滤数据

内连接不加关联条件的结果就是笛卡尔积

SELECT order_id,o.product_id,quantity,o.unit_price
FROM order_items o
（INNER） JOIN products p --实际INNER可写可不写，结果一致
 ON o.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;

自连接

通过给同一张表取两个别名来实现自连接

SELECT 
	e.employee_id,
    e.first_name,
    m.first_name
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id;

多表连接

通过多个JOIN语句来实现多表连接

SELECT 
	p.payment_id,
    c.name AS name, 
    p.invoice_id,
    p.date,
    p.amount,
    pm.name AS payment_method
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
JOIN clients c
	ON p.client_id = c.client_id;

复合连接

当表的记录是通过多列作为复合主键来作为唯一标识时，需要通过复合条件来实现连接

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id;

隐式连接

除了使用JOIN语句，还可以使用WHERE子句来实现连接

1
2
3

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id; -- 注意：不写WHERE子句会出笛卡尔积的情况

外连接 —— OUTER JOIN

悬浮元组：内连接时不满足条件而被舍弃的元组
左外连接：A $\rtimes$ B，假设有表A、B，通过左外连接(LEFT OUTER JOIN)会保留表A的悬浮元组
右外连接：A $\ltimes$ B，假设有表A、B，通过右外连接(RIGHT OUTER JOIN)会保留表B的悬浮元组

SELECT 
	p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT (OUTER) JOIN order_items oi --OUTER与INNER一样可写可不写
	ON p.product_id = oi.product_id;

多表外连接

根据实际需要查询的情况，来使用相应的外连接

USE sql_store;

SELECT 
	o.order_date,
    o.order_id,
    c.first_name,
    sh.name AS shipper,  
    os.name AS status
FROM orders o
LEFT JOIN shippers sh  
	-- 因为orders表里部分订单没有发货人信息，如果不使用外连接，我们无法得到所有订单的信息
	ON sh.shipper_id = o.shipper_id
JOIN order_statuses os
	ON os.order_status_id = o.status
JOIN customers c
	ON c.customer_id = o.customer_id;

自外连接

在数据库sql_hr中，employees表中有一条记录的report_to列为空值，当我们使用内连接时，就无法查询到该条记录的信息，但实际查询是要显示所有员工的信息，那这时候就需要使用外连接

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;

USING子句

用于JOIN语句过滤数据的另一个关键字，只能用于两个表中列名称相同的列

SELECT 
	p.date,
    c.name client,
    p.amount,
    pm.name payment_method
FROM payments p
JOIN clients c
	USING(client_id)
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
	--因为p表中的payment_method和pm表中的payment_method_id名称不同，故不能使用USING

自然连接

自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

SELECT 
	o.order_id,
    c.first_name
FROM orders o 
NATURAL JOIN customers c;

交叉连接

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积

--显式语法
SELECT 
	c.first_name customer,
    p.name product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;

--隐式语法
SELECT 
	c.first_name customer,
    p.name product
FROM customers c,products p
ORDER BY c.first_name;

联合

UNION，将多条select语句的结果，在字段数相同(即Select语句返回的结果列数相同)的情况下，在记录的层次上进行拼接。

USE sql_store;

SELECT 
	customer_id,
    first_name,
    points,
    "Bronze" AS types
FROM customers
WHERE points < 2000
UNION
SELECT 
	customer_id,
    first_name,
    points,
    "Silver" AS types
FROM customers
WHERE points between 2000 and 3000
UNION
SELECT 
	customer_id,
    first_name,
    points,
    "Gold" AS types
FROM customers
WHERE points > 3000
ORDER BY first_name;