SQL Set
SQL Set
This tutorial covers SQL Set operators.
Set operators allow concatenation of two queries. In other words, they combine the outcomes of two queries (such as two “select” statements).
This page encompasses:
- union
- union all
- except
- intersect
Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 2 | Patrick | Casey | 26 |
| 3 | Sam | Adams | 19 |
| 4 | Mike | Garcia | NULL |
| 5 | Patrick | Dumont | 21 |
| id | subjectName | grade | passed | studentID |
|---|---|---|---|---|
| 1 | History | C | 1 | 1 |
| 2 | Physics | A | 1 | 2 |
| 3 | History | F | 0 | 3 |
| 4 | Biology | A | 1 | NULL |
| 5 | Biology | E | 1 | 5 |
Union
The functionality of “union” allows the combination of two queries.
Rules for both queries:
- retrieve only distinct values (no duplicates)
- data types must be compatible
- columns and order must be the same
For the above reasons, we cannot use the “union” set operator with the statement “SELECT *” on the two tables. Nevertheless, we can perform the operation on a single column.
SELECT firstName FROM student_info UNION SELECT subjectName FROM grade;
| firstName |
|---|
| Biology |
| History |
| John |
| Mike |
| Patrick |
| Physics |
| Sam |
Notice that the column name stays from the first “select” and the values are alphabetically sorted. In addition, you can see that the the values do not repeat (e.g. 2 x Patrick or 2 x History).
Union All
The “union all” performs the same operation as “union.
But it includes all duplicated values in the outcome. The set operator follows the same query rules (without distinct).
SELECT firstName FROM student_info UNION ALL SELECT subjectName FROM grade;
| firstName |
|---|
| John |
| Patrick |
| Sam |
| Mike |
| Patrick |
| History |
| Physics |
| History |
| Biology |
| Biology |
In this scenario, duplicated values are shown. In addition, the results follow the order of the tables – all records from first query and then all records from second query.
Except
The “except” set operator outputs distinct values.
It retrieves only distinct values from the left (first) selected table that are not in the right (second) table.
Hence the name except.
SELECT firstName FROM student_info EXCEPT SELECT subjectName FROM grade;
| firstName |
|---|
| John |
| Mike |
| Patrick |
| Sam |
The results show only distinct values (e.g. 2 x Patrick) as well as only values from the first “select” query.
For instance, if there was a name “Mike” in the second table, the above statement will not show it in the output.
Intersect
The “intersect” statement works similar to the “except” statement.
It produces results that select only data that is present in both tables.
SELECT firstName FROM student_info INTERSECT SELECT subjectName FROM grade;
| firstName |
|---|
The outcome is empty. The reason: values must be in both queries (tables). Our columns (firstName & subjectName) do not have any records that are the same.
Next: SQL Joins
