SQL UNION Clause - The Coding Shala

Home >> Learn SQL >> SQL Union Clause

SQL UNION Clause

The SQL UNION clause is used to combine the result set of two or more select statements. The SQL UNION Operator does not return any duplicate rows. If we want to get duplicate rows then we have to use UNION ALL Clause. The SQL UNION Clause will only work if selected columns in the select statement have the same in numbers with having the same data type in the same order.
SQL UNION Clause - The Coding Shala

SQL UNION Syntax

The basic syntax of SQL UNION is as follows - 

SELECT column_name(s) FROM table1  
 UNION  
 SELECT column_name(s) FROM table2; 

SQL UNION ALL Syntax

The basic syntax of SQL UNION ALL is as follows - 

SELECT column_name(s) FROM table1  
 UNION ALL  
 SELECT column_name(s) FROM table2; 

The following two tables are used for the examples -
Table 1. 'emp' table 

 emp_id     emp_name     city     dept_no     salary  
 1          Akshay        Pune      101       50000  
 3          Nikhil        Pune      101       51000  
 5          Mohit         Delhi     103       40000  
 6          Shubham       Surat     105       42000  
 7          Akash         Mumbai    106       45000  

Table 2. 'detail' table 
emp_id     age  
 1          25  
 2          20  
 3          23  
 4          28  
 5          29  
 6          26  

SQL UNION Example

The following SQL Query will combine the results of both the tables using UNION Clause and return results without any duplicate rows - 

SQL >> select emp_id from emp  
        union  
        select emp_id from detail;  
 
Output >>   
 emp_id  
 1  
 2  
 3  
 4  
 5  
 6  
 7  

SQL UNION with different number of columns example

If you want to use UNION with the different number of columns means there is a column in one table that is not present in another table then we can use 'NULL as column_name' to add one more null column. The following SQL query is the example of SQL UNION with the different number of columns - 

 SQL >> select emp_id, emp_name from emp  
        union  
        select emp_id, null as emp_name from detail;  
 
Output >>   
 emp_id     emp_name  
 1           null  
 1           Akshay  
 2           null  
 3           null  
 3           Nikhil  
 4           null  
 5          null  
 5          Mohit  
 6           null  
 6          Shubham  
 7          Akash

SQL UNION ALL Example

The following SQL query will combine both the tables using UNION ALL and return the results with duplicate rows - 

 SQL >> select emp_id, emp_name from emp  
        union all  
         select emp_id, null as emp_name from detail;  
 
Output >>   
 emp_id     emp_name  
 1          Akshay  
 3          Nikhil  
 5          Mohit  
 6          Shubham  
 7          Akash  
 1          null  
 2          null  
 3          null  
 4          null  
 5          null  
 6          null 



Other Posts You May Like
Please leave a comment below if you like this post or found some error, it will help me to improve my content.

Comments

Popular Posts from this Blog

Shell Script to find sum, product and average of given numbers - The Coding Shala

Shell Script to Create a Simple Calculator - The Coding Shala

Add two numbers in Scala - The Coding Shala

New Year Chaos Solution - The Coding Shala

Richest Customer Wealth LeetCode Solution - The Coding Shala