*When you need to combine information from multiple tables or queries, SQL set operators are a useful tool.*

SQL queries let us choose the most important bits from large amounts of information. Of course, we can’t expect that all necessary data will be stored in one table. Let’s say we want to present every aspect of some key data group in one results table (e.g. making a report with the names of every customer served by every department of a large company). These records may be found in many different tables, so we need SQL set operators to merge them into one table or to find common elements.

During such operations, we take two or more results from `SELECT`

statements and create a new table with the collected data. We do this using an SQL set operator. Operators like `UNION`

, `MINUS`

or `INTERSECT`

are widely used in SQL queries. Let’s see how they work.

### The Sample Tables

Suppose we have a very simple database that stores information about books and movies. It has only two tables,

and **BOOKS**

, which contain book and movie titles (respectively) and an ID number. As you look at these tables, notice that one title appears in both:**MOVIES**

Table: BOOKS | ||
---|---|---|

ID | Title | |

1 | The Witcher | |

2 | Harry Potter | |

3 | Nineteen Eighty-Four | |

4 | The Great Gatsby |

Table: MOVIES | ||
---|---|---|

ID | Title | |

1 | Iron Man | |

2 | Harry Potter | |

3 | Dr Strange | |

4 | Matrix |

### The UNION Operator

What if we wanted to make one table from all the content in the

and **BOOKS**

tables? This is a perfect time to use **MOVIES**`UNION`

operator.

`UNION`

merges the results of two SELECT statements. **Important:** `UNION`

statements only return UNIQUE values. Below, you’ll see a Venn diagram representing this operation and the code that will make it happen:

SELECT * FROM BOOKS UNION SELECT * FROM MOVIES

Here is the result:

ID | Title |
---|---|

1 | The Witcher |

2 | Harry Potter |

3 | Nineteen Eighty-Four |

4 | The Great Gatsby |

5 | Iron Man |

6 | Dr Strange |

7 | Matrix |

All the book and movie titles are now in one table. Note that “Harry Potter” – an item which appears in both tables – is shown only once in the results. Like we mentioned earlier, the UNION operator does not return duplicate values.

### The UNION ALL Operator

You’ve probably guessed that `UNION ALL`

is very similar to `UNION`

, but with one exception: `UNION ALL`

returns all data from all tables, no matter if it is a duplicate or not. Let’s do the same operation as in the UNION example and see what we get:

SELECT * FROM BOOKS UNION ALL SELECT * FROM MOVIES

Result:

ID | Title |
---|---|

1 | The Witcher |

2 | Harry Potter |

3 | Nineteen Eighty-Four |

4 | The Great Gatsby |

5 | Iron Man |

6 | Harry Potter |

7 | Dr Strange |

8 | Matrix |

This time, the little wizard appears twice in the results table.

Now we know the main difference between these two operators. But when we should use `UNION`

and when `UNION ALL`

? Firstly, know that there is a huge difference in efficiency between them. Let’s say we need to merge two query results that each contain 10,000 elements. `UNION`

will eliminate any duplicates and sort all the elements in the results table. This sorting process takes a lot of time and works with a large number of elements.

In sum, `UNION`

can be four times slower than `UNION ALL`

, which doesn’t eliminate duplicates and doesn’t sort the data. If we don’t care about duplicates and we want to work fast, `UNION ALL`

will be the perfect solution. But if we know that having unique elements is our main goal, then `UNION`

will be much more helpful.

### The MINUS Operator

MINUS is a little bit different. Let’s say we want to see only book titles that are not also movie titles. We need to “minus” everything from the

table that is also in the **BOOKS**

table. The **MOVIES**`MINUS`

operator is designed for this type of task.

SELECT * FROM BOOKS MINUS SELECT * FROM MOVIES

The result:

ID | Title |
---|---|

1 | The Witcher |

2 | Nineteen Eighty-Four |

3 | The Great Gatsby |

Now “Harry Potter” doesn’t appear in the results table; it’s the title of a book and a movie. Thanks to the `MINUS`

operator we are able to see only those titles that occur in the first table and are not present in the second.

By the way, some databases use the keyword `EXCEPT`

instead of `MINUS`

. Don’t worry – the function and results are exactly the same.

### The INTERSECT Operator

OK, so we know how to add and subtract some elements using the `UNION`

and `MINUS`

operators. But what should we do if we need to know what two queries have in common?

This is the main role of the `INTERSECT`

operator. Let’s see how it works.

SELECT * FROM BOOKS INTERSECT SELECT * FROM MOVIES

And the result:

ID | Title |
---|---|

1 | Harry Potter |

You know by now that “Harry Potter” (and only “Harry Potter”) is present in both tables. So it is the only element in the results table.

### Tips About Using SQL Set Operators

There are a few things to remember about set operators:

- If the column names or aliases being compared are different, the result column will be called after the column in the
**first**`SELECT`

query. - You can use either query results or tables with set operators.
- The columns being compared must be the same type and of equal number.
- The results table will always have more higher-precision columns.

The `UNION`

, `MINUS`

, and `INTERSECT`

operators will always sort the returned results; `UNION ALL`

will not. If we want a certain sort order or type, we can always use an `ORDER BY`

at the end of the query. But keep in mind that this will sort the whole query! We can’t use `ORDER BY`

before a set operator or try to sort every `SELECT`

separately.

We should also mention that we can use set operators in all types of queries. We don’t need to select all of the records from both tables; we can work on the results of existing queries.

It’s amazing what SQL set operators can do. Thanks to them, we can easily find and present interesting data. If you want to get some hands-on experience in working with SQL set operators and other functions, Vertabelo Academy’s SQL Essentials courses are a good place to start.