What is the difference between union and unionall in MySQL

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; ```

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;```

③. 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; ```

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

④. 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; ```

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.

⑤. 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;```

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!