> For the complete documentation index, see [llms.txt](https://hunter-ducharme.gitbook.io/sql-basics/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hunter-ducharme.gitbook.io/sql-basics/table_variables_and_set_operators.md).

# Table Variables & Set Operators

## What are they?

Take a look at the code block below:

```sql
SELECT A1, A2, . . . , A(n)
FROM R1, R2, . . . , R(m)
WHERE <condition>;
```

All of the variables inside the `FROM` clause are called table variables. They help with making the query more readable, and they rename relations within the `FROM` clause when we have more than one instance of a relation.

The second construct is called a set operator. A few examples of them are: the `UNION` operator, the `INTERSECT` operator, and the `EXCEPT` operator.

## Adding Table Variables for Readability

Let's assume we want to make a query that outputs all the movies along with their movie ID, title, User ID, User name, and rating.

Our query would look like this:

```sql
SELECT Movie.mID, Title, User.uID, Name, Rating
FROM Movie, User, Review
WHERE Movie.mID = Review.mID and User.uID = Review.uID;
```

We would then get our expected table output. However, to make the query a little more readable, we can place variables inside the `FROM` clause, and replace all the table names with just the table variable.

Our query would look like this:

```sql
SELECT M.mID, Title, U.uID, Name, Rating
FROM Movie M, User U, Review R
WHERE M.mID = R.mID and U.uID = R.uID;
```

Notice how we added `M` after `Movie` inside the `FROM` clause. That is called adding a table variable, then where ever we used `Movie` in our entire select statement, we can just replace with `M`. We also did the same thing for `User U` and `Review R`.

## Adding Table Variables for Multiple Instances

In this next query, we want to find all movies that have the exact same rating. In order to do that, we need to have two instances of `Review`. We will call the first instance `R1` and the second instance `R2`. We also need to include `Movie` in the `FROM` clause to get the movie `Title`.

Our query would look like this:

```sql
SELECT DISTINCT Movie.mID, Title, R1.Rating
FROM Movie M, Review R1, Review R2 
WHERE R1.Rating = R2.Rating and R1.mID = M.mID and R1.mID <> R2.mID and R1.uID <> R2.uID;
```

We first added the two table variables: `R1` and `R2` to separate each instance. We are looking for the `mID`'s, `Title`'s, and `Rating`'s of each movie. We specify `DISTINCT` in the `WHERE` clause to remove duplicates. We then place a condition in the `WHERE` clause to specify that we want to output movies with the same rating. We include the join condition `R1.mID = M.mID` to make sure the movies are the same in each relation. We then specify two final clauses: `and R1.mID <> R2.mID and R1.uID <> R2.uID;`. `R1.mID <> R2.mID` tells the computer that the `R1.mID` is different from `R2.mID`, or that movie 1 needs to be different from movie 2. If we didn't specify this clause, we would get an output of movies that equal themselves, because movie 1 would have the same `Rating` as itself, thus satisfying the condition. Likewise, `R1.uID <> R2.uID` makes sure we don't get back two instances of a review by the same reviewer. <br>

## The Union Operator

The `UNION` operator allows us to create queries that will output a list of elements that come from multiple tables. Previously we could only separate these elements into different columns. However, by using the `UNION` operator, we can get elements from different tables listed into a single column together.

For example, if we wanted to get a single list of all the movie titles and reviewer names, we would create a query that looks like this:

```sql
SELECT Title FROM Movie
UNION
SELECT Name FROM User;
```

We would then get a table with only **one** column, and it would list each movie title and each reviewer name.

## Specifying a Column Name

By default, SQL would pick label the column either `Title` or `Name`. If you wanted to specify a label for the column, you would use the `AS` operator.

It would look like this:

```sql
SELECT Title AS list FROM Movie
UNION
SELECT Name AS list FROM User;
```

Notice how we placed `AS list` in each select clause, and this tells SQL to name the column `list`.

## The Intersect Operator

The `INTERSECT` operator takes away the necessity to specify a joint relation. It automatically knows that each select statement in the query is for the same movie.

If we wanted to create a query that searched for movies that were created before the year 2000, and had a `mID` of less than 105, we could use the `INTERSECT` operator.

Our query would look like this:

```sql
SELECT Title FROM Movie WHERE Year < 2000
INTERSECT
SELECT Title FROM Movie WHERE mID < 105;
```

We would then get a table in return with all the movies that were created before the year 2000, and had a `mID` less than 105.

The resulting movies would be: *Top Gun*, *Titanic*, and *The Lion King*.

## The Except Operator

The `EXCEPT` operator does exactly the opposite of the `INTERSECT` operator.

Lets create a query that looks for movies that were created before the year 2000, but **do not** have a `mID` less than 105.

Our query would look like this:

```sql
SELECT Title FROM Movie WHERE Year < 2000
EXCEPT
SELECT Title FROM Movie WHERE mID < 105;
```

In this case, the `EXCEPT` operator tells SQL to look for movies that were made before the year 2000, and then take away the movies with a `mID` less than 105. This then leaves us with a result of movies which were created before 2000, but have a `mID` greater than or equal to 105.

The resulting movie would be: *The Godfather*.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

```
GET https://hunter-ducharme.gitbook.io/sql-basics/table_variables_and_set_operators.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.