What is a Union in SQL
A Union is an operator in SQL.
A Union is use to combine the result set of two or more select statements in one single data set.
The select statement use in union query must have the same number of columns.
The columns data type in the two queries must match.
A Union combines data by column position rather than column name, that means the column order must be the same in both select statement.
A Union operator selects only distinct values from both select queries by default. Here, duplicate records get dropped.
To get all the values from both select statement including duplicates, use Union All.
Below diagram give you better idea to understand, How Union and Union All works
SQL Union and SQL Union All Syntax
SELECT Column_name(s) FROM table1
UNION OR UNION ALL
SELECT Column_name(s) FROM table2;
SQL Union and SQL Union All Example
1) In below example, student and teacher tables are used in union query to give the city details from both tables.
Student Table
Teacher Table
SQL Union Query
SELECT 'Student' AS Type, city FROM student
UNION
SELECT 'Teacher' AS Type, city FROM teacher;
2) Now in this example, we will only select single column that is city from student and teacher tables.
SQL Union Query
SELECT city FROM student
UNION
SELECT city FROM teacher;
Here, only 7 rows get extracted because duplicates are removed. The City London comes twice, one from student table and one from teacher table. Union help us to remove the duplicates.
3) Now in this example, we have used union all to extract all cities from student and teacher table.
Union All Query
SELECT city FROM student
UNION ALL
SELECT city FROM teacher;
Union All fetch all records including duplicates. No of rows are 8.
Difference Between SQL Union and SQL Union All
The major difference is SQL Union extracted unique records, it will skip the duplicate records but SQL Union All extracted all the records including duplicates. The Union All retrieve large volume of records hence normally we should avoid usage of Union All.
How to use SQL Union with Order By clause?
Yes, you can use Order By clause with Union and following is one of the example, where city column is sorted by ascending order.
SQL Union with Order By Query
SELECT city FROM student
UNION
SELECT city FROM teacher
ORDER BY city ASC;
Whether you use distinct keyword in SQL Union and SQL Union All?
There is no point in using distinct while doing union operation because Union itself remove duplicates and only give unique records.
If you use distinct while doing Union All operation then you might see duplicate records because Union all club all the records from both select query. Here, London city is coming twice because one is from student table and one is from teacher table.
How to use where clause with Union operation?
You can use where clause with Union and Union All operation. You just specify the condition after select query. Here, city = Mumbai is extracted from first query and then the result is combined with 2nd query. So for 1 entry from student and 3 entries are from teacher table.
