Types of JOINs in MySQL With Examples

MySQL databases store large volumes of data organized in tables, and analyzing data from multiple tables simultaneously is a common task. It's crucial to have efficient methods that enable users to extract records from two or more tables based on specific criteria. The JOIN clause addresses this need by fetching data from multiple tables through a shared column.

Why MySQL JOINs are helpful

JOINs allow data from multiple tables to be accessed with a single query, eliminating the necessity for multiple queries and simplifying data retrieval.

Advantages of using JOINs:

Improved speed: A single JOIN query retrieves data from multiple tables faster than multiple individual queries, delivering the same results more efficiently.
Increased efficiency: JOIN clauses utilize indexes, improving performance and accuracy in data retrieval.
Reduced server load: JOINs reduce the number of data transfers between the application and the MySQL server, decreasing the overall load on the server.

Understanding JOINs in MySQL is fundamental knowledge for any database specialist. This article will explore the types of JOINs supported in MySQL and demonstrate how they work.

Different types of JOINs in MySQL

MySQL supports several JOIN types. Each type determines how tables are related in a query:

INNER JOIN returns rows with matching values across all specified tables.
LEFT OUTER JOIN includes all rows from the left table and only matching rows from the right table. Non-matching rows from the right table are filled with NULLs.
RIGHT OUTER JOIN returns all rows from the right table and only the rows from the left table that meet the JOIN condition. Non-matching rows from the left table are filled with NULLs.
CROSS JOIN combines every row from one table with each row from another, resulting in a table with all possible row combinations.
SELF JOIN allows comparisons within the same table or the extraction of hierarchical data. Table aliases are used here to avoid repeating the same table name.

MySQL does not directly support FULL OUTER JOIN, which would return both matching and non-matching rows from the joined tables. However, you can achieve this effect by combining LEFT and RIGHT OUTER JOINs.

Now, let us explore each JOIN type.

INNER JOIN

INNER JOINs are used to fetch only common matching records. The INNER JOIN clause allows retrieving only those records from Table A and Table B that meet the join condition. It is the most widely used type of JOIN.

Here is the syntax for MySQL INNER JOIN:

SELECT columns
FROM tableA 
INNER JOIN tableB
ON tableA.column = tableB.column;

Let's take a look at how INNER JOIN works in practice. Throughout this guide, we'll use the MySQL test database sakila and dbForge Studio for MySQL, a multi-featured IDE for MySQL and MariaDB databases to illustrate our examples.

The below query uses INNER JOIN to retrieve the data about films and their associated categories as well as rental rates:

SELECT
  film.title AS Film_Title,
  category.name AS Category_Name,
  film.rental_rate AS Rental_Rate
FROM film
  INNER JOIN film_category
    ON film.film_id = film_category.film_id
  INNER JOIN category
    ON film_category.category_id = category.category_id
ORDER BY film.rental_rate
LIMIT 20;

OUTER JOINs

Though MySQL does not support FULL OUTER JOIN (as opposed to SQL Server, for instance), it offers alternatives: LEFT OUTER JOIN and RIGHT OUTER JOIN.

Unlike INNER JOIN, these types return both matching and non-matching rows. For non-matching rows in a joined table, NULL values will be displayed.

Let's take a closer look at how MySQL LEFT JOIN and RIGHT JOIN work.

LEFT JOIN (or LEFT OUTER JOIN)

LEFT JOINs allow retrieving all records from Table A along with those records from Table B for which the join condition is met. For the records from Table A that do not match the condition, the NULL values are displayed.

Here is the syntax for MySQL LEFT JOIN:

SELECT columns
FROM tableA
LEFT [OUTER] JOIN tableB
ON tableA.column = tableB.column;

Have a look at the below example where LEFT JOIN retrieves information about all movies along with any associated rental details if they exist. It also includes movies that may not have any rental records (in that case, there are NULL values in the rental columns).

SELECT
  film.title,
  rental.rental_date,
  rental.return_date
FROM film
  LEFT OUTER JOIN inventory
    ON film.film_id = inventory.film_id
  LEFT OUTER JOIN rental
    ON inventory.inventory_id = rental.inventory_id
ORDER BY film.title;

RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN is similar to a LEFT JOIN, but it returns all records from Table B along with those records from Table A for which the join condition is met. For the records from Table B that do not match the condition, the NULL values are displayed.

Here is the syntax for MySQL RIGHT JOIN:

SELECT columns
FROM tableA
RIGHT [OUTER] JOIN tableB
ON tableA.column = tableB.column;

Assume we want to list list all films along with their respective category names. MySQL RIGHT JOIN allows us to do this successfully as shown below:

SELECT
  film.film_id,
  film.title,
  category.name AS category_name
FROM category
  RIGHT JOIN film_category
    ON category.category_id = film_category.category_id
  RIGHT JOIN film
    ON film_category.film_id = film.film_id;

Combining LEFT and RIGHT JOINs

As mentioned earlier, the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator can be used in MySQL to get the results similar to FULL OUTER JOIN in SQL Server.

This combination returns all rows from both tables involved in the JOIN query, matching rows from one table with corresponding rows in the other table where possible. For rows that do not have a match in the other table, the result will still include the row but with NULL values in the columns of the table that do not have a matching row.

LEFT and RIGHT JOINs to replace FULL OUTER JOIN

The syntax of this query is as follows:

SELECT * FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
UNION
SELECT * FROM tableA
RIGHT JOIN tableB ON tableA.id = tableB.id

Here's an example query: we want to list all customers along with their rentals, even if some customers haven't rented anything or some rentals have no associated customer. To retrieve this data, we combine the LEFT JOIN with the RIGHT JOIN:

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  r.rental_id,
  r.rental_date
FROM customer c
  LEFT JOIN rental r
    ON c.customer_id = r.customer_id
UNION
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  r.rental_id,
  r.rental_date
FROM customer c
  RIGHT JOIN rental r
    ON c.customer_id = r.customer_id;

MySQL - combining LEFT and RIGHT JOIN with UNION to replace FULL OUTER JOIN

CROSS JOIN

MySQL CROSS JOIN, also known as a cartesian join, retrieves all combinations of rows from each table. In this type of JOIN, the result set is returned by multiplying each row of table A with all rows in table B if no additional condition is introduced.

When you might need that type of JOIN? Envision that you have to find all combinations of a product and a color. In that case, a CROSS JOIN would be highly advantageous.

Here is the syntax for MySQL CROSS JOIN:

SELECT columns
FROM tableA
CROSS JOIN tableB;

To illustrate how CROSS JOIN works, we want to list every actor alongside every film in the sakila database:

SELECT
  actor.actor_id,
  actor.first_name,
  actor.last_name,
  film.title
FROM actor
  CROSS JOIN film;

SELF JOIN

JOINs are typically used to combine data from two or more tables. In contrast, a SELF JOIN combines data within a single table, allowing rows from the same table to be joined based on specific conditions.

Notice that SELF JOIN suggests creating aliases for the table, allowing users to differentiate between multiple instances of the same table.

SELF JOIN is especially useful for working with hierarchical structures where entries in the same table relate to each other. This JOIN type simplifies comparisons of data within a single table, helping to reveal relationships between different rows.

Here is the syntax for MySQL SELF JOIN:

SELECT columns
FROM tableA AS alias1
JOIN tableA AS alias2
ON alias1.column = alias2.column;

Let us find pairs of actors who share the same last name:

SELECT
  a1.first_name AS First_Name_1,
  a1.last_name AS Last_Name,
  a2.first_name AS First_Name_2,
  a2.last_name
FROM actor a1
  JOIN actor a2
    ON a1.last_name = a2.last_name
WHERE a1.actor_id != a2.actor_id;

Conclusion

It is vitally important for any analyst and DBA to have a thorough understanding of JOINs and use them freely in everyday work. That's where dbForge Studio for MySQL becomes indispensable. Its advanced code completion works impeccably even for complex JOIN clauses. You don't need to memorize hundreds of column names or aliases, dbForge Studio for MySQL will prompt you a full JOIN clause. Its rich functionality significantly simplifies building complex queries and managing JOIN conditions. Also, you can watch this video tutorial and download our free brief guide to MySQL JOINs: