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                
 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值