Ga naar inhoud

Header

Merging Data with RIGHT-, INNER- and FULL JOIN

Introduction

We have learned to use a LEFT JOIN.

This is the most common way to combine data from tables.

We now look at some other ways:

Header



1. RIGHT JOIN

Header

A LEFT JOIN returns all results from a left table, with matching results from a right table.

  • A RIGHT JOIN is the opposite of a LEFT JOIN.

A RIGHT JOIN returns all results from a right table, with matching results from a left table.



1.1. LEFT JOIN example

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
... ... ... ... ... ... 
Columns: 6
Rows: 103



1.2. RIGHT JOIN example

SELECT
  *
FROM
  movies_genres
  RIGHT JOIN movies ON movies.id = movies_genres.movie_id;
movie_id genre id name year rank
10920 Sci-Fi 10920 Aliens 1986 8.2
10920 Action 10920 Aliens 1986 8.2
10920 Thriller 10920 Aliens 1986 8.2
10920 Horror 10920 Aliens 1986 8.2
... ... ... ... ... ... 
Columns: 6
Rows: 103

This gives the same result (in a different order) as the LEFT JOIN.



1.3. When to use RIGHT JOIN

In practice, you will mostly use a LEFT JOIN, not a RIGHT JOIN.

A RIGHT JOIN can make your query clearer if you want to merge more than 2 tables in 1 query.



2. INNER JOIN

Header

A LEFT JOIN returns all results from a left table, with matching results from a right table.

  • An INNER JOIN returns only results where there is a match in results from the left table and right table.


An INNER JOIN is therefore the same as:

  • LEFT JOIN followed by filtering out empty values on added columns from the right table.



2.1. INNER JOIN example

SELECT
  movies_after_2000.*,
  movies_genres.genre
FROM
  movies_genres
  INNER JOIN (
    SELECT * FROM movies WHERE year > 2000
  ) movies_after_2000 ON movies_after_2000.id = movies_genres.movie_id;

This example uses a subquery, making movies_after_2000 now refer to a table with only movies from after 2000 instead of all movies.

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
... ... ... ... ... ... 
Columns: 6
Rows: 35

You only see genres for which movies are also available that were released after 2000:

  • The query returns 35 rows as a result.

  • However, the movies_genres table contains 103 rows. Check this yourself using SELECT COUNT(*) FROM movies_genres.

  • The remaining 68 rows are linked to movies that were released before 2000 and are therefore not in the result of the INNER JOIN query.



2.2. When to use INNER JOIN

In practice, you will mostly use a LEFT JOIN, not an INNER JOIN.

An INNER JOIN is used when you want only results that appear in both tables.



3. FULL JOIN

Header

A LEFT JOIN returns all results from a left table, with matching results from a right table.

A FULL JOIN returns all results from a left table, with all results from a right table.



2.1. FULL JOIN example

SELECT
  *
FROM
  movies
  FULL 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
... ... ... ... ... ... 
Columns: 6
Rows: 103
  • You see all movies, and also all genres.
  • For movies for which no genres are known, the columns from the movies_genres table are empty.
  • For genres for which no movies are known, the columns from the movies table are empty.



2.2. When to use FULL JOIN

In practice, you will mostly use a LEFT JOIN, not a FULL JOIN.

A FULL JOIN is used when you want all results that appear in both tables.



Summary

Header

  • LEFT JOIN is the most used.
  • RIGHT JOIN is the opposite of a LEFT JOIN.
  • Sometimes used for better clarity when merging more than 2 tables.
  • INNER JOIN.
  • When you want only results that appear in both tables.
  • FULL JOIN.
  • When you want all results from a left table, with all results from a right table.