Modules

The SQL UNION operator is a tool used to combine the data from two separate tables.

Let's say Table 1 have values A, B, C & D and Table 2 has C, D, E & F.

What's the difference between UNION & UNION ALL:

• If you union these 2 tables, using the UNION operator, it will stack these 2 tables and remove duplicate.
• UNION ALL will return A,B,C,D,C,D,E,F and doesn't remove any duplicate, just stack the values!

Case Example:

Let's say you have 2 tables: employees and clients

employees table has the following columns:

And here is a sample of the clients table:

You're being asked to pull the list of all the client_name and their corresponding city, here is how it looks in SQL:

SELECT employee_name, city FROM employees

UNION ALL 

SELECT client_name, city FROM clients

When I do it using UNION ALL, it doesn't remove duplicate so my result will be as follow:

John Doe got repeat twice because UNION ALL doesn't remove duplicate. If you run it using UNION, it will remove John Doe from the list.

Let's practice now!