SELECT
o.order_id,
c.first_name,
sh.name AS shipper
FROM orders o
JOIN customers c
-- ON o.customer_id = c.customer_id
USING(customer_id)#括号里写上列名称
LEFT JOIN shippers sh
USING(shipper_id)
/*如果两个表中列名称完全一样,
我们可以使用一个更简单的using子句替换on子句
内连接外连接都可以使用using子句*/
连接条件添加多列怎么办?
SELECT *
FROM order_items oi
JOIN order_item_notes oin
-- ON oi.order_id = oin.order_id AND
-- oi.product_id = oin.product_id
USING(order_id, product_id)
练习,得到如下表格
SELECT
p.date,
c.name AS client,
p.amount,
pm.name AS payment_methord
FROM payments p
JOIN clients c USING(client_id)
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
自然连接
不建议使用
SELECT * FROM orders o NATURAL JOIN customers c /*系统自动根据相同的列去连接*/
交叉连接
第一张表中的每条记录都会和第二条表中的每条记录结合
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
/*顾客表里的每条记录都会和产品表里的每条记录结合*/
交叉连接隐式写法
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c, products p
ORDER BY c.first_name
/*交叉连接的隐士写法*/