• 【十】【SQL】合并查询和内连接

    在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union ，union all

    Union

    该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

    Union all

    该操作符用于取得两个结果集的并集。当使用该操作符时，不会自动去掉结果集中的重复行。

    将工资大于2500或职位是MANAGER的人找出来

     

    1. mysql> select *from emp where sal>2500;
    2. +--------+-------+-----------+------+---------------------+---------+------+--------+
    3. | empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
    4. +--------+-------+-----------+------+---------------------+---------+------+--------+
    5. | 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
    6. | 007698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
    7. | 007788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
    8. | 007839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
    9. | 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
    10. +--------+-------+-----------+------+---------------------+---------+------+--------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select *from emp where job='MANAGER';
    14. +--------+-------+---------+------+---------------------+---------+------+--------+
    15. | empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
    16. +--------+-------+---------+------+---------------------+---------+------+--------+
    17. | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
    18. | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
    19. | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
    20. +--------+-------+---------+------+---------------------+---------+------+--------+
    21. 3 rows in set (0.00 sec)
    22.  
    23. mysql> select *from emp where sal>2500 union select *from emp where job='MANAGER';
    24. +-------+-------+-----------+------+---------------------+---------+------+--------+
    25. | empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
    26. +-------+-------+-----------+------+---------------------+---------+------+--------+
    27. |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
    28. |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
    29. |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
    30. |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
    31. |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
    32. |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
    33. +-------+-------+-----------+------+---------------------+---------+------+--------+
    34. 6 rows in set (0.00 sec)
    35.  
    36. mysql> select *from emp where sal>2500 union all select *from emp where job='MANAGER';
    37. +-------+-------+-----------+------+---------------------+---------+------+--------+
    38. | empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
    39. +-------+-------+-----------+------+---------------------+---------+------+--------+
    40. |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
    41. |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
    42. |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
    43. |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
    44. |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
    45. |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
    46. |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
    47. |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
    48. +-------+-------+-----------+------+---------------------+---------+------+--------+
    49. 8 rows in set (0.00 sec)
    50.  
    51. mysql> select ename,job,sal from emp where sal>2500 union all select *from emp where job='MANAGER';
    52. ERROR 1222 (21000): The used SELECT statements have a different number of columns
    53. mysql> select ename,job,sal from emp where sal>2500 union all select ename,job,sal from emp where job='MANAGER';
    54. +-------+-----------+---------+
    55. | ename | job       | sal     |
    56. +-------+-----------+---------+
    57. | JONES | MANAGER   | 2975.00 |
    58. | BLAKE | MANAGER   | 2850.00 |
    59. | SCOTT | ANALYST   | 3000.00 |
    60. | KING  | PRESIDENT | 5000.00 |
    61. | FORD  | ANALYST   | 3000.00 |
    62. | JONES | MANAGER   | 2975.00 |
    63. | BLAKE | MANAGER   | 2850.00 |
    64. | CLARK | MANAGER   | 2450.00 |
    65. +-------+-----------+---------+
    66. 8 rows in set (0.00 sec)
    67.  
    68. mysql> 
     

    在数据库中，UNION和UNION ALL是用来合并两个或多个SELECT语句的结果集的操作。但它们之间有一个重要的区别：
     

    UNION在合并结果集时会自动去除重复的记录。
     

    UNION ALL会包含所有的记录，包括重复的记录。
     

     

    当你使用UNION合并sal>2500和job='MANAGER'的查询结果时，即使某些记录可能同时满足这两个条件，结果集中每条记录只会出现一次。
     

    当你使用UNION ALL时，即使某些记录重复，也会将所有满足条件的记录包含在结果集中。所以，如果有记录既满足sal>2500也满足job='MANAGER'，那么这条记录会在结果集中出现两次。
     

     

    最后一个查询错误的原因是因为UNION和UNION ALL要求所有SELECT语句选择相同数量和类型的列。在最后一个查询中正确地修改了这一点，使得两个SELECT语句都选择了ename，job，和sal列，从而避免了错误。
     

    内连接和笛卡尔积的关系
     

    内连接（Inner Join）和笛卡尔积（Cartesian Product）是数据库查询中两个相关但不同的概念。理解它们之间的关系有助于更好地掌握如何有效地使用SQL进行数据查询。
     

    笛卡尔积
     

    笛卡尔积是两个表中所有可能的记录组合。如果第一个表有M条记录，第二个表有N条记录，那么它们的笛卡尔积将包含M*N个结果。在SQL中，如果没有指定连接条件进行表的连接操作，就会产生笛卡尔积。这通常不是我们想要的结果，因为它会返回大量无关的数据组合。
     

    内连接
     

    内连接是基于至少一个共同属性（连接条件）来筛选笛卡尔积中的记录，只返回那些在两个表中都匹配连接条件的记录。内连接实质上是在笛卡尔积的基础上应用了一个过滤器，这个过滤器就是连接条件。通过这种方式，内连接避免了笛卡尔积产生的数据爆炸问题，确保了查询结果的相关性和精确性。
     

    关系
     

    笛卡尔积是基础：在没有任何连接条件的情况下，两个表的连接操作会产生笛卡尔积，即两个表所有可能的行组合。
     

    内连接是筛选后的笛卡尔积：内连接通过在笛卡尔积上应用连接条件来筛选结果，只返回符合条件的记录。这意味着内连接是笛卡尔积的一个子集，这个子集只包含那些在两个表中根据指定条件能找到对应关系的记录。
     

    显示SMITH的名字和部门名称
     

     

    1. mysql> select *from emp;
    2. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    3. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
    4. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    5. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
    6. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
    7. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
    8. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
    9. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
    10. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
    11. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
    12. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
    13. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
    14. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
    15. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
    16. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
    17. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
    18. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
    19. +--------+--------+-----------+------+---------------------+---------+---------+--------+
    20. 14 rows in set (0.00 sec)
    21.  
    22. mysql> select *from dept;
    23. +--------+------------+----------+
    24. | deptno | dname      | loc      |
    25. +--------+------------+----------+
    26. |     10 | ACCOUNTING | NEW YORK |
    27. |     20 | RESEARCH   | DALLAS   |
    28. |     30 | SALES      | CHICAGO  |
    29. |     40 | OPERATIONS | BOSTON   |
    30. +--------+------------+----------+
    31. 4 rows in set (0.00 sec)
    32.  
    33. mysql> select *from emp,dept where emp.deptno=dept.deptno;
    34. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    35. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
    36. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    37. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    38. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    39. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    40. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    41. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    42. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    43. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    44. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    45. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    46. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    47. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    48. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    49. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    50. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    51. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    52. 14 rows in set (0.00 sec)
    53.  
    54. mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno;
    55. +--------+------------+
    56. | ename  | dname      |
    57. +--------+------------+
    58. | SMITH  | RESEARCH   |
    59. | ALLEN  | SALES      |
    60. | WARD   | SALES      |
    61. | JONES  | RESEARCH   |
    62. | MARTIN | SALES      |
    63. | BLAKE  | SALES      |
    64. | CLARK  | ACCOUNTING |
    65. | SCOTT  | RESEARCH   |
    66. | KING   | ACCOUNTING |
    67. | TURNER | SALES      |
    68. | ADAMS  | RESEARCH   |
    69. | JAMES  | SALES      |
    70. | FORD   | RESEARCH   |
    71. | MILLER | ACCOUNTING |
    72. +--------+------------+
    73. 14 rows in set (0.00 sec)
    74.  
    75. mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';
    76. +-------+----------+
    77. | ename | dname    |
    78. +-------+----------+
    79. | SMITH | RESEARCH |
    80. +-------+----------+
    81. 1 row in set (0.00 sec)
    82.  
    83. mysql> 
    84.  
    85. mysql> 
    86. mysql> 
    87. mysql> select *from emp inner join dept on emp.deptno=dept.deptno;
    88. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    89. | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
    90. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    91. | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    92. | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
    93. | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
    94. | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    95. | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
    96. | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    97. | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    98. | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    99. | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    100. | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
    101. | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    102. | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
    103. | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
    104. | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
    105. +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
    106. 14 rows in set (0.00 sec)
    107.  
    108. mysql> select ename,dname from emp inner join dept on emp.deptno=dept.deptno;
    109. +--------+------------+
    110. | ename  | dname      |
    111. +--------+------------+
    112. | SMITH  | RESEARCH   |
    113. | ALLEN  | SALES      |
    114. | WARD   | SALES      |
    115. | JONES  | RESEARCH   |
    116. | MARTIN | SALES      |
    117. | BLAKE  | SALES      |
    118. | CLARK  | ACCOUNTING |
    119. | SCOTT  | RESEARCH   |
    120. | KING   | ACCOUNTING |
    121. | TURNER | SALES      |
    122. | ADAMS  | RESEARCH   |
    123. | JAMES  | SALES      |
    124. | FORD   | RESEARCH   |
    125. | MILLER | ACCOUNTING |
    126. +--------+------------+
    127. 14 rows in set (0.00 sec)
    128.  
    129. mysql> select ename,dname from emp inner join dept on emp.deptno=dept.deptno where ename='SMITH' ;
    130. +-------+----------+
    131. | ename | dname    |
    132. +-------+----------+
    133. | SMITH | RESEARCH |
    134. +-------+----------+
    135. 1 row in set (0.00 sec)
    136.  
    137. mysql> 
     

    内连接（INNER JOIN）是数据库中一种常用的连接类型，它用于连接两个或多个表，并返回满足连接条件的所有行。当使用内连接时，只有当两个表中都有匹配的值时，这些行才会出现在查询结果中。
     

     

    select *from emp,dept where emp.deptno=dept.deptno;：这是一个隐式的内连接示例，使用了WHERE子句来指定连接条件。这个查询返回了emp表和dept表中deptno列相匹配的所有行。
     

    select *from emp inner join dept on emp.deptno=dept.deptno;：这是一个显式的内连接示例，使用了INNER JOIN关键词和ON子句来指定连接条件。这个查询的效果和上面的查询是相同的，也返回了emp表和dept表中deptno列相匹配的所有行。
     

     

    内连接和笛卡尔积（CROSS JOIN）的区别在于：
     

    内连接（INNER JOIN）返回两个表中满足连接条件的行。
     

    笛卡尔积（CROSS JOIN）返回两个表中所有可能的行组合，不需要满足任何连接条件。如果不指定连接条件，结果集的大小将是第一个表的行数乘以第二个表的行数。
     

     

    使用WHERE子句进行的隐式连接实际上是内连接的一种，但是显式地使用INNER JOIN可以使查询的意图更加明确，也更容易理解和维护。
     

    结尾
     

    最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
     

     

    同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
     

     

    谢谢您的支持，期待与您在下一篇文章中再次相遇！
    
                
    
                    
    
                
    • 

                
  • 

                    相关阅读:
    
                    
架构权衡分析ATAM-架构真题（六十）                            
    
数据库专辑--WITH CHECK OPTION的用法                            
    
当AI遇到IoT：开启智能生活的无限可能                            
    
MEGC(FACIAL MICRO-EXPRESSION GRAND CHALLENGE)微表情识别比赛相关网站                            
    
关于windows上运行bitsandbytes老是报错的（说cuda版本有问题）解决方案                            
    
Python实战-新能源王者宁德时代股权穿透研究(附完整代码)                            
    
SpringMVC之文件上传下载                            
    
Zulip Server 6.0 发布，开源团队协作工具                            
    
数据结构-堆                            
    
python 处理json数据                            
    
                        
                
    • 
                
  • 
                    原文地址：https://blog.csdn.net/m0_74163972/article/details/136460190
                
    •