Home > Database > Mysql Tutorial > What is the difference between union and unionall in MySQL

What is the difference between union and unionall in MySQL

PHPz
Release: 2023-05-30 08:04:05
forward
1925 people have browsed it

union: Perform a union operation on multiple result sets, excluding duplicate rows, and sort them at the same time.

union all: Perform a union operation on multiple result sets, including duplicate rows, without sorting.

Query the information of employees whose department is less than 30, and the information of employees whose department is greater than 20 and less than 40.

①. First query the information of employees with department number less than 30.

   SELECT 
               employees_id
               ,last_name
               ,salary
               ,department_id
     FROM      employees
    WHERE      department_id < 30; ```
Copy after login

What is the difference between union and unionall in MySQL

Query employee information of departments greater than 20 and less than 40.

   SELECT 
           employees_id
           ,last_name
           ,salary
           ,department_id
     FROM  
           employees
    WHERE 
           department_id BETWEEN 20 and 40;```
Copy after login

What is the difference between union and unionall in MySQL

③. Use union to connect two tables

    SELECT 
            employees_id
            ,last_name
            ,salary
            ,department_id
      FROM  
            employees
     WHERE  
            department_id < 30
     UNION
    SELECT 
            employees_id
            ,last_name
            ,salary
            ,department_id
      FROM  
            employees
     WHERE  
            department_id BETWEEN 20 and 40; ```
Copy after login

The results are sorted by default and deduplicated. Both tables have department information No. 30. The result Appears only once.

What is the difference between union and unionall in MySQL

④. Next, use union all to connect the two tables

     SELECT 
              employees_id                 
              ,last_name                 
              ,salary                  
              ,department_id     
       FROM  
              employees  
      WHERE  
              department_id < 30    
 UNION  ALL
     SELECT                   
              employees_id                  
              ,last_name                  
              ,salary                  
              ,department_id    
       FROM  
              employees   
      WHERE  
              department_id BETWEEN 20 and 40; ```
Copy after login

The results are not deduplicated or sorted. The sorted results are compared with the results below. Go first Query the information of employees between 20 and 40, and check the information of employees less than 30.

What is the difference between union and unionall in MySQL

⑤. Compare query results

     SELECT 
             employees_id
             ,last_name
             ,salary
             ,department_id
       FROM  
             employees
      WHERE  
             department_id BETWEEN 20 and 40
  UNION ALL
     SELECT 
             employees_id
             ,last_name
             ,salary
             ,department_id
       FROM  employees
      WHERE  department_id < 30;```
Copy after login

What is the difference between union and unionall in MySQL

The default is not sorting.

The above is the detailed content of What is the difference between union and unionall in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:yisu.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template