codescracker


sql

SQL Union



« Previous Tutorial Next Tutorial »


SQL UNION operator is used to combine the result of the two or more SQL SELECT statements.

Here is the general form of the UNION operator in SQL:

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

Note - By default, the SQL UNION operator selects only distinct values. Therefore, to allow duplicate values then use the ALL keyword with UNION.

SQL UNION ALL Operator

Here is the general form of the UNION ALL operator in SQL:

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

The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION

SQL Demo Database

Let's assume we have the following two table named "Students" and "Teachers". This is SQL Table1 (Students Table):

sql union

This is SQL Table2 (Teachers Table):

sql union operator

SQL UNION Operator Example

Here is an example of UNION operator in SQL. The following SQL statement selects all the different address (only distinct values) from the tables "Students" and "Teachers"

SELECT address FROM students
UNION
SELECT address FROM teachers
ORDER BY address;

After querying the above SQL statements, you will get the following output:

sql union keyword

SQL UNION ALL Operator Example

Here is an example of UNION ALL operator in SQL. The following SQL statement uses the UNION ALL to select all (duplicate values also) address from the tables "students" and "teachers"

SELECT address FROM students
UNION ALL
SELECT address FROM teachers
ORDER BY address;

After querying the above SQL statements, you will get the following output:

sql union all

SQL UNION ALL with WHERE (CONDITION)

Here is an example of UNION ALL operator with SQL WHERE clause. The following SQL statement uses the UNION ALL operator to select all (duplicate values also) Students and Teachers Name and Address from the tables "Students" and "Teachers", if they have the same ID (i.e., StudentID = TeacherID)

SELECT Name, Address FROM Students
WHERE StudentID=1
UNION ALL
SELECT Name, Address FROM Teachers
WHERE TeacherID=1
ORDER BY Name;

After querying the above SQL statements, you will get the following output:

sql union example

Here, since in both the table, there is only one ID that matches, which is 1.


« Previous Tutorial Next Tutorial »



Tools
Calculator

Quick Links
Signup - Login - Give Online Test