# 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;
```
***
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://notes.nomanaziz.me/development/database/sql/basics/5.-set-operations.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
