SQL UNION and UNION ALL |
SQL Sample Code - SQL UNION and UNION ALL |
UNION – Retrieves all unique records only.
| SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2; |
UNION ALL – Retrieves all records including duplicate values.
| SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2; |
The Select Statements used in a UNION must have the same inherent structure;
- Same Column Names
- Same Number of Columns
- Compatible Data Type
See the SQL samples section below for an explanation of UNION and UNION ALL.
What do SQL UNION and UNION ALL do? |
The Select Statements used in a UNION must have the same inherent structure;
- Same Column Names
- Same Number of Columns
- Compatible Data Types
A UNION brings together two separate Select statements into one table.
This is often needed to bring together the same types of information from separate places.
Sample 1 – SQL UNION |
In this SQL sample we will use the database tables ‘tblCustomers’ and 'tblStaff'.
tblStaff
| Staff_ID | Staff_Forename | Staff_Surname | Company_ID | Staff_Age | StaffGender |
| 1 | Joe | Sample | 1 | 21 | M |
| 2 | Fred | Code | 2 | 32 | M |
| 3 | Ben | SQL | 3 | 35 | F |
| 4 | Frederica | Reference | 1 | 21 | F |
| 5 | Barbara | Sample | 32 | F |
tblCustomers
| Cust_ID | Cust_Forename | Cust_Surname | Cust_Age | CustGender |
| 5 | Mohammed | Khan | 54 | M |
| 100 | Tariq | Malik | 32 | M |
| 22 | Joe | Bloggs | 21 | M |
| 3 | Pretty | Umbogu | 18 | F |
| 654 | Zena | Johns | 64 | F |
In this SQL sample we want to retrieve the Forenames, Surnames and Ages of all unique people in the tblStaff table and tblCustomers table and combine them into one table.
Use the SELECT statement below:
| SELECT Staff_Forename, Staff_Surname, Staff_Age FROM tblStaff UNION SELECT Cust_Forename, Cust_Surname, Cust_Age FROM tblCustomer; |
The table of results will look like this:
| Staff_Forename | Staff_Surname | Staff_Age |
| Joe | Sample | 21 |
| Fred | Code | 32 |
| Ben | SQL | 35 |
| Frederica | Reference | 21 |
| Barbara | Sample | 32 |
| Mohammed | Khan | 54 |
| Tariq | Malik | 32 |
| Pretty | Umbogu | 18 |
| Zena | Johns | 64 |
Note that Joe Sample has only appeared once in the results even though he was in both tables.
The results table’s column headings will default to the column names of the first Select statement, in this case the tblStaff table columns.
Sample 2 – SQL UNION ALL |
Following on from the example above, if we use the UNION ALL to retrieve all records including duplicates.
Use the SELECT statement below:
| SELECT Staff_Forename, Staff_Surname, Staff_Age FROM tblStaff UNION ALL SELECT Cust_Forename, Cust_Surname, Cust_Age FROM tblCustomer; |
The table of results will look like this:
| Staff_Forename | Staff_Surname | Staff_Age |
| Joe | Sample | 21 |
| Fred | Code | 32 |
| Ben | SQL | 35 |
| Frederica | Reference | 21 |
| Barbara | Sample | 32 |
| Mohammed | Khan | 54 |
| Tariq | Malik | 32 |
| Joe | Sample | 21 |
| Pretty | Umbogu | 18 |
| Zena | Johns | 64 |
Note that Joe Sample has appeared twice in the results whereas using UNION he was omitted due to it being a duplicate.
The results table’s column headings will default to the column names of the first Select statement, in this case the tblStaff table columns.
Related SQL Sample Code:
|
|