Joins on tables in SQL basically are the key to doing meaningful queries. This web page will try to cover the basics of the joins in SQL and give you some examples to help you understand what's going on. The data set we are going to use are the following tables from the movie database you're dealing with in HW3:
Inventory
| TapeID | MovieID |
Movies
| MovieID | MovieName |
Without much ado, here's the basics of joins...
Handling a 'Join' in the WHERE Clause
A simple example of this is the answer to the question "What are all the
movie names in the inventory?". An easy way to write this query is...
SELECT DISTINCT MovieName
FROM Inventory I, Movies M
WHERE I.MovieID = M.MovieID;
In actuality, this query is NOT joining the two tables together. All it is doing is looking through the Inventory table and each record is compared to the records in the Movie table. It only allows matching records to come through for the answer. Although this is a very natural way to get the answer, as with other SQL answers, there are other ways...
How is a Join represented in SQL?
A join is where two tables are put together according to some rules. To make a
join in SQL, you'll have to write it in the FROM clause of your query. Here is the basic
format of a join...
<Table 1> <Join Relation> <Table 2> ON <Statement>
There are basically three different types of joins supported in SQL Server...
1) INNER JOIN
2) LEFT JOIN
3) RIGHT JOIN
When SQL runs a query, it grabs the tables in the FROM clause before doing anything else. Thus, it runs the join statement to produce a temporary table that it will use for the rest of the query execution. The actions and examples of the different JOIN expressions are discussed in the following sections.
What is an INNER JOIN?
An INNER JOIN is another way of doing what you did above. What this does is put
the two tables at the same level and joins them together on matching instances of your
statement. All non-matching items from either table are removed from the resulting
table. This idea can be thought of as the following...
Thus, the question "What are all the movie names in the inventory?" could be answered by the following query as well.
SELECT DISTINCT MovieName
FROM Inventory I INNER JOIN Movies M ON I.MovieID = M.MovieID;
What is a LEFT JOIN?
A LEFT JOIN is a totally different way of doing a query from an INNER JOIN. A
LEFT JOIN defines the table to the left to be the upper table and the table on the right
to be the lower table. This idea is shown as:
The join will take a record from the upper table and will look for a match in the lower table according to the join statement. A match in the lower table will result in a record in the resultant table. Any records of the upper table that do not match with any of the records in the lower table are inserted into the resultant table with the values from the lower table filled in with a NULL value. Thus, if you had the following tables of data...
Movies
| MovieID | MovieName |
| 1 | "Rush Hour" |
| 2 | "Nutty Professor" |
| 3 | "There's Something About Mary" |
Inventory
| TapeID | MovieID |
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
and you wrote the the query...
SELECT *
FROM Movies M LEFT JOIN Inventory I ON M.MovieID = I.MovieID
ORDER BY M.MovieID;
It would return the following table...
| MovieID | MovieName | TapeID | MovieID |
| 1 | "Rush Hour" | 1 | 1 |
| 2 | "Nutty Professor" | 2 | 2 |
| 2 | "Nutty Professor" | 3 | 2 |
| 3 | "There's Something About Mary" | NULL | NULL |
What is a RIGHT JOIN?
A RIGHT JOIN is just like a LEFT JOIN, except it goes the other direction. Thus,
the tables can be thought of the following way:
Thus, you could write the same query as the LEFT JOIN as...
SELECT *
FROM Inventory I RIGHT JOIN Movies M ON I.MovieID = M.MovieID
ORDER BY M.MovieID;
What's so cool about having NULL values in the table?
This type of a join makes solving problems like "What movies are not in the
inventory?" not so bad. For those of you who are using MySQL, this type of a
query would be a pain without LEFT/RIGHT JOIN because MySQL does not support nested
queries. The thing that makes these joins useful is that you can check for the NULL
values in SQL. 'IS NULL' checks for a NULL value and 'IS NOT NULL' checks for a
value. Thus, instead of the answer being a nested query like...
SELECT DISTINCT MovieName
FROM Movies
WHERE MovieID NOT IN
(SELECT MovieID
FROM Inventory);
You could write...
SELECT DISTINCT M.MovieName
FROM Movies M LEFT JOIN Inventory I ON M.MovieID = I.MovieID
WHERE TapeID IS NULL;
Further documentation on these topics can be found through the help menu in SQL Server.
