UNION, UNION ALL

If we have more selections and we want to combine their results, we can use UNION. It is used if we are only interested in unique values.

If we want all records and do not consider the uniqueness of the result, we use UNION ALL. What needs to be met?

Must take care to ensure that the selections match the type. This means that they must have the same number of fields, data types, and also all fields should be in the same order.

Structure:

SELECT column_name FROM table_name1
UNION
SELECT column_name FROM table_name2;

 

Example1:

We have tables with this data:

Table Staff

 

Table Friends

 

SELECT Staff_Name FROM Staff
UNION
SELECT Friend FROM Friends;

As a result, we see that unique values return to us.

 

Example2:

We have tables with this data:

Table Staff

 

Table Friends

 

SELECT Staff_Name FROM Staff
UNION ALL
SELECT Friend FROM Friends;

 

 

Example3:

We have tables with this data:

Table Customer

 

Table Friends

 

SELECT NAME FROM Customer
UNION
SELECT Friend FROM Friends;

As a result, we see that unique values return to us.

 

Example4:

We have tables with this data:

Table Customer

 

Table Friends

 

SELECT NAME FROM Customer
UNION ALL
SELECT Friend FROM Friends;

 

 

Example5:

We have tables with this data:

Table Customer

 

Table Friends

 

SELECT NAME FROM Customer
WHERE HAIR = 'brown'
UNION 
SELECT Friend FROM Friends
WHERE FRIEND = 'Frank';

We can also use the WHERE condition:

 

Example6:

We have tables with this data:

Table Customer

 

Table Friends

 

SELECT NAME FROM Customer
WHERE HAIR = 'black'
UNION ALL
SELECT Friend FROM Friends
WHERE FRIEND = 'Thomas';

We can also use the WHERE condition:

 

Example7:

We have tables with this data:

Table Customer

 

Table Friends

 

SELECT NAME AS Hero_Name FROM Customer
WHERE HAIR = 'black'
UNION 
SELECT Friend FROM Friends
WHERE FRIEND = 'Thomas';

We can also use the WHERE condition: