Prev Next
The SQL UNION ALL is also same as SQL UNION clause. But, UNION ALL returns all records from both tables including duplicates. Remaining properties are same as UNION clause.
SQL Syntax for UNION ALL:
| SQL Syntax for UNION ALL | SELECT column1, column2, etc FROM table1 UNION ALL SELECT column1, column2, etc FROM table2; |
Please consider following 2 tables with few records as given below.
Table1:
Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Table2:
Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows
Example for how to use SQL UNION ALL:
As described earlier, UNION ALL will return all records, including duplicates as mentioned in below output.
SQL query:
SELECT Student_ID, City FROM student1
UNION ALL
SELECT Student_ID, City FROM student2;
SQL query Output:
- Output of SQL statement – SELECT Student_ID, City FROM student1;
- Output of SQL statement – SELECT Student_ID, City FROM student2;
- Final Output using UNION ALL: (Total records – 10)
