MYSQL笔记_多表查询-优快云博客

本文详细介绍了SQL中的多表查询技巧，包括等值连接、内连接、外连接（左连接、右连接、全连接），以及如何通过添加连接条件、明确字段来源和使用UNION/UNIONALL去除重复来优化查询性能。还提供了SQL99新特性和实际操作练习示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

举例：查找商品name

SELECT customer_id,order_id
       FROM orders
       WHERE customer_id=7

       SELECT order_id,product_id
       FROM order_items
       WHERE order_id=7

       SELECT 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