INNER JOIN
案例:
inner 可省略不写
前提:在本案例中的两个表里的id列是一致的
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
-- 把订单表和顾客表连接
因为是SELECT *,因此返回了两个表的所有列
1-现在修改SELECT *
SELECT order_id , first_name , last_name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
2-再次修改SELECT 后面的语句
SELECT order_id , first_name , last_name , customer_id
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
原因:
因为量表都有customer_id一列,因此MySQL不知道该返回哪一列
修改方法:
在列前增加表格名
SELECT order_id , first_name , last_name , customers.customer_id
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
3-使用别名减少重复,使代码更简洁
例如,用o作为order的别名;用c作为customers的别名
SELECT order_id , first_name , last_name , c.customer_id
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
练习:
连接表order_items和表products,并返回订单id、产品id、数量、单价
SELECT order_id, o.product_id, quantity, o.unit_price
FROM order_items o
JOIN products p
ON o.product_id = p.product_id