举例:查找商品name
SELECT customer_id,order_id
FROM orders
WHERE customer_id=7SELECT order_id,product_id
FROM order_items
WHERE order_id=7SELECT product_id, `name`
FROM products
WHERE product_id=3
冗余,速度慢,维护麻烦
笛卡尔积(交叉连接)错误
错误一
SELECT customer_id,product_id
FROM orders,order_items
错误二
SELECT customer_id,product_id
FROM orders CROSS JOIN order_items
错误原因:缺少多表的连接条件
加上连接条件
SELECT customer_id,product_id
FROM orders,order_items
WHERE orders.`order_id` = order_items.`order_id`
注意:连接用.
出现多个表中都存在的字段,需要指明字段来源于哪一张表
例如order_id
SELECT orders.`customer_id` , order_items.`product_id`
FROM orders,order_items
WHERE orders.`order_id` = order_items.`order_id`
从sql优化角度建议:多表查询时,每个字段都指明来源于哪一张表
SELECT orders.`customer_id` , order_items.`product_id` , orders.`order_id`
FROM orders,order_items
WHERE orders.`order_id` = order_items.`order_id`
起别名以增加可读性
SELECT T1.`customer_id` , T2.`product_id`
FROM orders AS T1 , order_items AS T2
WHERE T1.`order_id` = T2.`order_id`
注意:使用WHERE时字段不可以起别名,表可以起别名
如果表使用了别名,则WHERE中也必须使用别名
n个表实现多表查询,至少需要n-1个连接条件
用法:查询员工的customer_id,product_id,city
SELECT T1.`customer_id`,T2.`product_id`,T3.`city`,T3.`last_name`
FROM orders AS T1,order_items AS T2,customers AS T3
WHERE T1.`order_id` = T2.`order_id`
AND T3.`customer_id`= T1.`customer_id`
多表查询的分类
1,等值连接 VS 非等值连接
非等值连接
用法:查找工资在B档(3000-5999)的员工
SELECT last_name,salary,grade_level
FROM employees AS e,job_grades AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
或者
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal
2,自连接 VS 非自连接
自连接(自我引用)
应用:一个表内写有员工和上级的信息,信息包括员工id和所属上级的id
查询员工上级id时,查询内容和输出答案在同一张表上
SELECT emp.last_name,emp.employee_id,mgr.last_name,mgr.manager_id
FROM employees AS emp,employees AS mgr
WHERE emp.employee_id = mgr.manager_id
3,内连接 VS 非内连接
内连接:合并具有同一列的,两个以上的表,结果集中不包含两个表不匹配的行
SQL92
SELECT T1.`customer_id` , T2.`product_id`
FROM orders AS T1 , order_items AS T2
WHERE T1.`order_id` = T2.`order_id`
SQL99
JOIN ON
SELECT T1.`customer_id` , T2.`product_id` ,T3.`city`
FROM orders AS T1 INNER JOIN order_items AS T2
ON T1.`order_id` = T2.`order_id`
INNER JOIN customers AS T3
ON T1.`customer_id` = T3.`customer_id`
外连接:合并具有同一列的,两个以上的表,
结果集中包含两个表不匹配的行之外,
还查询到了表1或表2中不匹配的行
分类:
LEFT OUTER JOIN ON左外连接
SELECT T1.`customer_id` , T2.`product_id`
FROM orders AS T1 LEFT OUTER JOIN order_items AS T2
ON T1.`order_id` = T2.`order_id`
RIGHT OUTER JOIN ON右外连接
SELECT T1.`customer_id` , T2.`product_id`
FROM orders AS T1 RIGHT OUTER JOIN order_items AS T2
ON T1.`order_id` = T2.`order_id`
FULL OUTER JOIN ON满外链接
mysql不支持FULL OUTER JOIN ON
SELECT T1.`customer_id` , T2.`product_id`
FROM orders AS T1 FULL OUTER JOIN order_items AS T2
ON T1.`order_id` = T2.`order_id`
UNION 去除重复部分
UNION ALL 不会去除重复部分
能用UNION ALL就用UNION ALL,
因为UNION需要排除重复数据,效率不足
SQL99新特性
NATURAL JOIN 查询所有相同字段,进行等值连接,不够灵活
SELECT O.order_id,P.`name`,O.quantity
FROM order_items O NATURAL JOIN products P
USING
SELECT customer_id,product_id
FROM orders o JOIN order_items oi
USING (order_id)
练习
1,内连接
SELECT customer_id,product_id
FROM orders o JOIN order_items oi
ON o.`order_id` = oi.`order_id`
2,外连接
左
SELECT customer_id,product_id
FROM orders o LEFT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
右
SELECT customer_id,product_id
FROM orders o RIGHT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
3,左外连接+去重
SELECT customer_id,product_id
FROM orders o LEFT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
WHERE oi.`order_id` IS NULL
用WHERE去重
4,满外链接
SELECT customer_id,product_id
FROM orders o LEFT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
UNION ALL
SELECT customer_id,product_id
FROM orders o RIGHT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
WHERE oi.`order_id` IS NULL
5, 满外链接+去重
SELECT customer_id,product_id
FROM orders o LEFT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
WHERE oi.`order_id` IS NULL
UNION ALL
SELECT customer_id,product_id
FROM orders o RIGHT OUTER JOIN order_items oi
ON o.`order_id` = oi.`order_id`
WHERE oi.`order_id` IS NULL