1.3. Joins

A join combines two tables into one result set. We can use joins when we want to query two tables at the same time. Whenever we join two tables, we have to specify the condition upon which the tables need to be joined.

In SQL, there are four different types of joins:

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join

No matter which join you are working with, the general syntax for the query looks like so:

SELECT column_name_1, column_name_2, ....
FROM table_a
TYPEOFJOIN JOIN table_b ON table_a.column_name_1 = table_b.column_name_1;

In this general query, we specified what columns we want (or we could have used the * to read data from all columns). We have also specified that table_a is the left table and that table_b is the right table. On line 3, we need to include the type of join as part of our query with the JOIN keyword and the condition upon which we are joining the tables. Our condition follows the ON keyword and tells MySQL what we believe to be matching records. This may mean we want to join on matching customer ids or matching dollar amounts or matching dates depending on the tables we are working with and what questions we need to answer.

1.3.1. Inner Join

Joining two tables with an inner join produces a result set that only includes the values that are present in both tables.

Example

If we use an inner join to combine johnson_wedding and johnson_vow_renewal in a query, we can see what guests are going to both the vow renewal and the wedding.

SELECT last_name, first_name
FROM johnson_vow_renewal
INNER JOIN johnson_wedding ON johnson_vow_renewal.guest_id = johnson_wedding.guest_id;

This query will give us a result set of the first and last names of the guests from the johnson_vow_renewal table that are also in the johnson_wedding table.

The Venn diagram above shows the result set highlighted in blue.

1.3.2. Left Outer Join

Joining two tables with a left outer join gives us a result set which includes all values in the left table and any matching records from the right table.

Example

If we use a left outer join to combine johnson_wedding and johnson_vow_renewal in a query, the result set includes all of the guests invited to the wedding and any guests who were also invited to the vow renewal.

SELECT last_name, first_name
FROM johnson_wedding
LEFT JOIN johnson_vow_renewal ON johnson_wedding.guest_id = johnson_vow_renewal.guest_id;

The Venn diagram above shows the result set highlighted in blue.

1.3.3. Right Outer Join

Joining two tables with a right outer join gives us a result set that includes all values in the right table and any matching records from the left table.

Example

If we use a right inner join to combine johnson_wedding and johnson_vow_renewal in a query, the result set includes all of the guests that were invited to the vow renewal and any guests who were also invited to the wedding.

SELECT last_name, first_name
FROM johnson_wedding
RIGHT JOIN johnson_vow_renewal ON johnson_wedding.guest_id = johnson_vow_renewal.guest_id;

The Venn diagram above shows the result set highlighted in blue.

1.3.4. Full Outer Join

Joining two tables with a full outer join gives us a result set that includes all records from both tables. Full outer joins are important to SQL, but the syntax is not supported in MySQL. Instead, to achieve a full outer join, you have to work with a left outer join and a right outer join. To show what a full outer join looks like in other types of SQL, we have simulated some possible syntax below.

Example

Now that another event planner has joined Mary’s company, to get all of the events run by the company in August, we can use a full outer join to combine mary_events and leah_events.

SELECT *
FROM mary_events
FULL OUTER JOIN leah_events ON mary_events.month = leah_events.month
WHERE mary_events.month = 08;

The Venn diagram above shows the result set highlighted in blue.

Note

If you do want to try out a full outer join, the syntax to simulate it looks some like this:

SELECT * FROM table_a LEFT JOIN table_b ON table_a.column_name_1 = table_b.column_name_1
UNION
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.column_name_1 = table_b.column_name_2;

UNION is used to bring together the result sets of 2 SELECT queries. Check out the documentation for more information on how UNION works.

1.3.5. Check Your Understanding

Question

True or false, an inner join gives all of the records in both tables

Question

What bugs are in this SQL query? Select all that apply.

SELECT
FROM johnson_vow_renewal
OUTER JOIN johnson_weding ON johnson_vow_renewal.guest_id = johnson_wedding.guest_id

1. Nothing specified after SELECT.
2. johnson_vow_renewal is spelled wrong.
3. The type of join is not specified.
4. johnson_wedding is spelled wrong.
5. ON is in the wrong place.
6. There isn’t a semicolon on the end of the SQL query.