SQL Joins

Learning Objectives

• Discuss the convention for relating data in a relational database
• Add Foreign Key constrains to an SQL database
• Form SQL queries that use the SQL Join command

Pre-requisites

Familiarity with Basic SQL Commands

Lesson

In a previous lesson we created tables to hold menu data for a Restaurant application. In this lesson we will implement the relationships between these tables.

Take a look at the UML diagram below, you can see that there is a 1 to many relationship between the Restaurant and its Menus.

How can we implement this relationship? We could make a new field on the restaurants table called 'menu'. But what if we wanted to add a second menu 'menu2'? And a third 'menu3'?

Instead, the menus table (child) needs a column which links it to the restaurants table (parent). The convention is to name the column with an '_id' suffix i.e.'restaurant_id'. When we create a row in the menus table we reference the id of the Restaurant we want the Menu to be associated with. This 'relates' the two tables and is why databases with relationships between tables are called 'relational databases'.

We can use the FOREIGN KEY constraint to specify the link between two tables. A FOREIGN KEY must refer to the PRIMARY KEY in another table. If we declare a column as a FOREIGN KEY, then, if an attempt is made to delete the related row in the other table we will get an error. The FOREIGN KEY constraint maintains the integrity of relationships between tables.

Here is an example of how to add a FOREIGN KEY:

CREATE TABLE menus (id INT PRIMARY KEY, title TEXT, restaurant_id INT, FOREIGN KEY (restaurant_id) REFERENCES restaurants(id));

How do we use the association we just created? When we query our database we use the 'JOIN' keyword in SQL. The JOIN keyword makes an association between the PRIMARY KEY from one table and the FOREIGN KEY of another. Read about the SQL Join command to understand its syntax.

The statement below selects the columns we want to retrieve from both the restaurants table and the menus table:

SELECT restaurants.name, menus.name 
FROM restaurants 
JOIN menus ON restaurants.id = menus.restaurant_id 
WHERE restaurants.id = 1;

Notice now we have mixed fields coming from different tables, we therefore have to name our columns with both the table_name.field_name i.e. menus.name. In this example the PRIMARY KEY is the restaurants table id and the menus table restaurant_id. Can you explain why restaurant_id is referred to as the foreign key?

Assignment

1. Modify your tables as follows:
   • add a restaurant_id column to the menus table and mark it as a FOREIGN KEY
   • add a menu_id column to the MENU_ITEM table and mark it as a FOREIGN KEY

Be aware that Repl does not fully support the foreign key constraint (i.e. may allow you to delete linked data).

2. Write some JOIN statements that do the following:

   • SELECT the name of a menu from a specific restaurant and list all the menu items on this menu
   • SELECT all the restaurants with a count of the number of menus each restaurant has. Hint: you will need to use the COUNT and GROUP BY keywords.
   • SELECT all the menus, with the total cost of all the menu items summed, and have the list in descending order (most expensive first). Hint: you will need to use the SUM, GROUP BY and ORDER BY keywords.

3. Share your Repl with your coach for review

Assignment extension tasks

Research the different types of SQL JOIN function there are.

Additional resources

We recommend the following videos for learning about SQL Joins:

• What is a Database Foreign Key?
• SQL Inner Join
• SQL Joins for beginners