> For the complete documentation index, see [llms.txt](https://notes.nomanaziz.me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.nomanaziz.me/development/database/sql/basics/5.-set-operations.md).
# 5. Set Operations
### Union
**(*****combine result sets of multiple queries into a single result set.*****)**
*
```
```
* Combines result sets of two or more `SELECT` statements into a single result set
* The queries must conform to the following rules:
* The number and order of columns in the select list of both queries must be same
* The data types must be compatible
* It also removes all the duplicate rows from the combined data set
* To retain the duplicate rows, you should use the `UNION ALL` instead
#### UNION with ORDER BY clause
* `UNION` operator may place the rows from the result ret of the first query before or after or between the rows from the result set of the second query
* To sort the rows in the final result set, you use the `ORDER BY` caluse in the second query
* If you place the ORDER BY clause at the end of each query, the combined result set will not be sorted as you expected.
#### Examples
We have a table `top_rated_films`
We have a table `most_popular_films`
**1. Combine data from both tables**
```sql
SELECT * from top_rated_films
UNION
SELECT * from most_popular_films;
```
**2. Combine result sets from both tables including duplicates**
```sql
SELECT * from top_rated_films
UNION ALL
SELECT * from most_popular_films;
```
**3. Combine result sets from both tables including duplicates and ordering them**
```sql
SELECT * from top_rated_films
UNION ALL
SELECT * from most_popular_films
ORDER BY title;
```
### Intersect
**(*****combine the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.*****)**
*
```
```
* This operator combines result sets of two or more SELECT statements into a single result set
* It returns any rows that are available in both result sets.
#### PostgreSQL INTERSECT with ORDER BY clause
* If you want to sort the result set returned by the `INTERSECT` operator, you place the `ORDER BY` at the final query in the query list like in the `UNION` operator
#### Example (Get popular films which are also top rated films)
```sql
SELECT *
FROM most_popular_films
INTERSECT
SELECT *
FROM top_rated_films;
```
### Except
**(*****return the rows in the first query that does not appear in the output of the second query.*****)**
*
```
```
* The `EXCEPT` operator returns distinct rows from the first (left) query that are not in the output of the second (right) query.
* The queries need to follow these rules
* The number of columns and their orders must be same in two queries
* The data types of respective columns must be compatible
#### Example (Find top-rated films that are not popular)
```sql
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films
ORDER BY title;
```
***
