Join

All the queries up until this point have been useful except for one major limitation - that is, we have been selecting from only one table at a time with the SELECT statement. It is time to introduce one of the most beneficial features of SQL and relational database systems - the Join. To put it simply, the Join makes relational database systems "relational".

Joins allow you to link data from two or more tables together into a single query result - from one single SELECT statement.

A database is defined as a set of related data stored in tables of rows and columns. Most often the data we are looking for will be in more than one table. So, for example if we wanted to see the book details for a given book copy. We would want information from the tables: book and bookcopy.

The JOIN keyword selects all rows from both tables if there is a match between the join columns in both tables (i.e. Primary Key - Foreign Key link).

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

The JOIN statement

Recall the attributes for the book and bookcopy tables:

Book(ISBN, title, publisher, publishedDate, category, price)

BookCopy(copyId, ISBN, dateAcquired, dateDestroyed)

Notice how each of the tables have a common ISBN column. This column, will be used to JOIN the two tables. It is the primary key in the Book table and the foreign key in the BookCopy table.

If we wish to return the copyId and title for each bookcopy, we need values from both tables so we perform the statement as follows:

select copyId, title 
from book join bookcopy 
on book.isbn=bookcopy.isbn;

This particular Join is also known as an Inner Join or Equijoin. This is the most common type of Join that you will see or use.

Note: The JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the Book table that do not have matches in the BookCopy table, these Books (rows) will NOT be listed.

The following statements are NOT the same - run them both and see the different results. Count the number of records returned.

1. Without the ON clause:

   select copyId, title 
   from book join bookcopy;

1. With the ON clause:

   select copyId, title 
   from book join bookcopy
   on book.isbn=bookcopy.isbn;

Another alternative of writing the query is to use the NATURAL JOIN. In this case, the join columns must have the same name. The statement omits the ON clause.

select copyId, title 
from book natural join bookcopy;

Finally, another (older) method of writing the query is as follows:

select copyId, title 
from book, bookcopy 
where book.isbn=bookcopy.isbn;

Example One

We want to return the title and copyId of all books containing the word JavaScript in the title.

There are four things to do here:

1. Identify which tables have the data you are looking for, in this case it is Book and BookCopy. These need to be joined.
2. Identify which columns are the primary and foreign keys in these tables; in this case it is ISBN in the Book and ISBN in the BookCopy table. We use these in the ON clause.
3. Identify which columns we want the query to output; in this case it is copyId from the BookCopy table; and title from the Book table. We will use these in our SELECT.
4. Add any conditions necessary, in this example: title like '%JavaScript%';

select title, copyId 
from book join bookcopy 
on book.isbn=bookcopy.isbn
where title like '%JavaScript%';

Example Two

In this example, we wish to return the copyId, title, publisher, and acquired date for all book copies that are Computing books.

Again, there are four things to do here:

1. Identify which tables have the data you are looking for, in this case its Book and BookCopy. These need to be joined.
2. Identify which columns are the primary and foreign keys in these tables; in this case it is ISBN in the Book and ISBN in the BookCopy table. We use these in the ON clause.
3. Identify which columns we want the query to output; in this case it is copyId and acquiredDate from the BookCopy table; and title and publisher from the Book table. We will use these in our SELECT.
4. Add any conditions necessary, in this example: category = 'Computing';

select copyid, title, publisher, dateacquired                            
from book join bookcopy                                                   
on book.isbn = bookcopy.isbn                                                             
where category = 'Computing';

Exercises

1. Retrieve the title, copyId, publisher, and acquired date for all book copies that were acquired since June 1 2014.
   Sort the results in alphabetical order of title.

   

2. List the students (combined fname and lname) who have borrowed a book(s). Return the student name only once even if he/she has more than one loan.

   

3. List the students (combined fname and lname) who have borrowed a book(s) and has it still on loan (dateBack is empty). Return the student name only once even if he/she has more than one loan.

   

4. For each loan record, retrieve the student by Name (combined fname and lname) who made the loan, and the copyId, dateOut and dateBack for each book loaned. Sort the results in alphabetical order of Name (fname sorted within lname).

   

5. List the copyId and title for all books that are in service now (datedestroyed is empty).

   

Example Three

In this example, we wish to return the number of book copies per Book title and output the count (result) with the label Number of Books.

There are three things to do here:

1. Identify which tables have the data you are looking for, in this case its Book and BookCopy. These need to be joined.
2. Identify which columns are the primary and foreign keys in these tables; in this case it is ISBN in the Book and ISBN in the BookCopy table. We use these in the ON clause.
3. Identify which columns we want the query to output; in this case it is title from the Book table and count(copyId) from the ookCopy table. We will use these in our SELECT.

Let's put it all together:

select title, count(copyId)  as "Number of Books"  
from book join bookcopy                                                            
on book.isbn = bookcopy.isbn                                             
group by title;

Note: The records are also sorted by title.

Exercises

1. Return the book title and corresponding number of book copies for all books that have Database in the book title. Output the count with the label Number of Books.

   

2. Return the number of loans per student (identified by name). Output the count with the label Number of Loans and identify each student by name (combined fname and lname). Note: Use the combined fname and lname in the Group By clause. Sort in alphabetical order by Last Name and then First Name.

   

3. Return the number of loans that a student has at present (where dateBack is empty). Output the count with the label Number of Loans and again identify each student by name.

   

Movies Exercise

For these exercises, load the movies database and remember to enter the command:

USE MOVIES;

To do:

1. For each rating, return the reviewer id, film title, director, and number of stars.

2. Return the number of reviews (in the ratings table) left by Chris Jackson. Label the output "Number of Chris's Reviews.

3. Return the number of reviews left by each reviewer (identified by name). Label the number of reviews (Number of Reviews).

4. Return the number of reviews left for each film (identified by title). Label the number of reviews (Number of Film Reviews).

Solutions

Solutions for the exercises in this lab are available here: Week3.zip