x

SQL – UNION ALL

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

sql-table1

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows

sql-table48


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;

sql-table49

  • Output of SQL statement – SELECT Student_ID, City FROM student2;

sql-table50

  • Final Output using UNION ALL: (Total records – 10)

sql-table52

Prev     Next



Like it? Please Spread the word!