内连接(显式)
SELECT order_id, o.product_id, name, quantity, o.unit_price
FROM order_items o
JOIN products p
ON o.product_id = p.product_id
将两张表格的内容,通过某一共同数值进行连接,使用函数FROM ……JOIN……ON……,ON后面写入用于连接的共同数值。表格的名称可进行简写,order_items o,表示order_items这张表名称简写为o,在前后代码中都进行简写,更加简洁。SELECT不同列时,注意如果某一列在两个表中都出现,需要注明从哪一张表中选择,否则会出现混淆。
跨数据库连接
SELECT *
FROM order_items o
JOIN sql_inventory.products p
ON o.product_id = p.product_id
如果需要跨数据库连接,为不属于当前数据库的表添加前缀。如sql_inventory.products,其他同上。
自连接
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
在某张表内部进行连接,以下图中表为例:reports_to 可以和employee_id 在这张表中进行自连接,几个注意点:FROM和JOIN中针对这张表要用不同名字进行重命名,ON中也用对应的命名进行匹配。
多表连接
SELECT p.payment_id, c.name, c.address, c.city, c.state, c.phone, pm.name
FROM payments p
JOIN clients c
ON p.client_id = c.client_id
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
通过多个JOIN……ON,来进行多表的连接,注意命名和列的选择。
复合连接条件
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……ON……AND……
隐含连接语法
SELECT *
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
与内连接(显式)含义相同,使用语法 FROM……WHERE……,但是建议用显式,因为会强制要求输入ON,不容易出错。
外连接
SELECT
p.product_id, p.name, oi.quantity
FROM products p
LEFT JOIN order_items oi
ON p.product_id = oi.product_id
LEFT JOIN 或者 RIGHT JOIN 被称为外连接,LEFT JOIN表示FROM表中内容从呈现完整,RIGHT JOIN表示其后表中的内容呈现完整,如果只有JOIN,则为内部连接。
如图中product_id为product表中返回的内容,第7项空缺,因为order_item表中缺少值。
多表外连接
SELECT
o.order_date,
o.order_id,
c.first_name,
s.name AS shipper,
os.name AS status
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id
LEFT JOIN shippers s
ON o.shipper_id = s.shipper_id
LEFT JOIN order_statuses os
ON o.status = os.order_status_id
最好使用LEFT JOIN来加入多个外表连接以免出现混淆,原理和外连接相同。
自我外连接|self outer joins
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字句|The using clause
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
using在不同的表之间,列名称相同时可以使用,如果需要两列进行匹配,可以使用USING(a,b)
自然连接
SELECT *
FROM orders o
NATURAL JOIN customers c
自然连接:可以缩短代码,由系统自行内连接有相同名称的列,但是有风险,不建议使用。
交叉连接|cross joins
SELECT *
FROM shippers
CROSS JOIN products
含义:(个人的思考)shippers*join中的每一行
此为显示交叉连接(推荐使用)
SELECT *
FROM shippers, products
此为隐式交叉连接
联合|unions
SELECT customer_id, first_name, points, 'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT customer_id, first_name, points, 'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT customer_id, first_name, points, 'Gold' AS type
FROM customers
WHERE points >3000
ORDER BY first_name
针对相同的表或者不同的表进行输出结果合并,列数需要相同,否则会出错;列名默认为第一个SELECT的列名。