Merging Data with LEFT JOIN

Introduction

There are different ways to merge data.

We look at the LEFT JOIN.

The LEFT JOIN is most commonly used in practice.

You use a LEFT JOIN as follows:

• You have a table (left table).
• You want to add data from another table to this table (right table).
• With a LEFT JOIN:
  • You select all rows from the left table.
  • With a match for each row with data from the right table.

We will look at this from an example.

---

1. Example LEFT JOIN

We have tables movies and movies_genres:

---

1.1. Table movies

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
30959	Batman Begins	2005	(NULL)
...	...	...	...

Columns: 4
Rows: 36

---

1.2. Table movies_genres

movie_id	genre
10920	Sci-Fi
10920	Action
10920	Thriller
10920	Horror
17173	Comedy
18979	Drama
...	...

Columns: 2
Rows: 103

---

1.3. Expected Result

• In table movies we have data of 36 films.
• In table movies_genres we have 103 genres of films.
• Each film can have multiple genres.
  • This is a one-to-many relationship.

If we were to look up the genres for each film, it should lead to the following result:

id	name	year	rank	movie_id	genre
10920	Aliens	1986	8.2	10920	Sci-Fi
10920	Aliens	1986	8.2	10920	Action
10920	Aliens	1986	8.2	10920	Thriller
10920	Aliens	1986	8.2	10920	Horror
17173	Animal House	1978	7.5	17173	Comedy
...	...	...	...	...	...

---

1.4. Applying LEFT JOIN

We get this result with a LEFT JOIN:

SELECT
  *
FROM
  movies
  LEFT JOIN movies_genres ON movies.id = movies_genres.movie_id;

id	name	year	rank	movie_id	genre
10920	Aliens	1986	8.2	10920	Sci-Fi
10920	Aliens	1986	8.2	10920	Action
10920	Aliens	1986	8.2	10920	Thriller
10920	Aliens	1986	8.2	10920	Horror
17173	Animal House	1978	7.5	17173	Comedy
...	...	...	...	...	...

Columns: 6
Rows: 103

• We merge left table movies with right table movies_genres.
  • With LEFT JOIN.
• Both tables contain a column with the id of a film.
  • With ON we establish this link.
• We select all columns from the result.
• You also get results from the left table, where there is no match in the right table.

The syntax for a LEFT JOIN is as follows:

SELECT
  *
FROM
  <left_table>
  LEFT JOIN <right_table> ON <left_table>.<column> = <right_table>.<column>;

• The order of the tables around LEFT JOIN is important.
  • First name the left table, then the right table.
  • The order of specifications in the ON statement does not matter.

---

2. Aliases

With an alias you give a column or table a different name during your query.

• This is often used with joins.

We look at the following example:

SELECT
  *
FROM
  movies AS m
  LEFT JOIN movies_genres AS mg ON m.id = mg.movie_id;

id	name	year	rank	movie_id	genre
10920	Aliens	1986	8.2	10920	Sci-Fi
10920	Aliens	1986	8.2	10920	Action
10920	Aliens	1986	8.2	10920	Thriller
10920	Aliens	1986	8.2	10920	Horror
17173	Animal House	1978	7.5	17173	Comedy
...	...	...	...	...	...

Columns: 6
Rows: 103

This gives the same result as before.

• We use alias m for table movies.
• We use alias mg for table movies_genres.

---

3. Selection of Columns

So far we selected all columns:

id	name	year	rank	movie_id	genre
10920	Aliens	1986	8.2	10920	Sci-Fi
10920	Aliens	1986	8.2	10920	Action
10920	Aliens	1986	8.2	10920	Thriller
10920	Aliens	1986	8.2	10920	Horror
17173	Animal House	1978	7.5	17173	Comedy
...	...	...	...	...	...

You see that columns id and movie_id contain the same values.

We solve this with the following code:

SELECT
  m.*,
  mg.genre
FROM
  movies AS m
  LEFT JOIN movies_genres AS mg ON m.id = mg.movie_id;

id	name	year	rank	genre
10920	Aliens	1986	8.2	Sci-Fi
10920	Aliens	1986	8.2	Action
10920	Aliens	1986	8.2	Thriller
10920	Aliens	1986	8.2	Horror
17173	Animal House	1978	7.5	Comedy
...	...	...	...	...

Columns: 5
Rows: 103

• With m.* we select all columns from table movies.
• With mg.genre we select only column genre from table movies_genres.

---

4. Grouping

We now have a table with films and their genres.

It might be useful to know, for example:

• How many genres does each film have?

We can answer this question by grouping, with GROUP BY.

• GROUP BY is executed later than JOIN.
• Therefore, aliases can also be used in GROUP BY.

SELECT
  m.name,
  COUNT(mg.genre) AS genres_count
FROM
  movies AS m
  LEFT JOIN movies_genres AS mg ON m.id = mg.movie_id
GROUP BY
  m.name
ORDER BY
  genres_count DESC;

name	genres_count
Little Mermaid, The	6
Shrek	6
Vanilla Sky	5
Batman Begins	5
...	...

Columns: 2
Rows: 36

You see that we now nicely sorted the number of genres per film.

---

Summary

• LEFT JOIN:
  • First table (left table).
  • Second table (right table).
  • With a LEFT JOIN:
    • You select all rows from the left table.
    • With a match for each row with data from the right table.
    • You also get results from the left table, where there is no match in the right table.
• Example:

SELECT
  *
FROM
  movies
  LEFT JOIN movies_genres ON movies.id = movies_genres.movie_id;

• The order of naming the tables is important.
  • First left table, then right table.
• You can use aliases for table names.
• You can use LEFT JOIN in combination with other SQL statements.
  • Such as GROUP BY and ORDER BY.