How To Use MySQL JOINS {With Examples}

Introduction

JOINS in MySQL are used to combine information located in multiple tables and retrieve that information in a single result.

Relational databases contain several logically related tables linked together, and each table contains unique data or common data. JOINS help retrieve data from tables based on a common field between them.

In this tutorial, you will learn what MySQL JOINS are and how to use them.

Prerequisites

• MySQL Server and MySQL shell installed
• A MySQL user account with root privileges

What Are JOINS in MySQL?

The JOIN statement in MySQL is a method of linking data between several tables in a database based on common column's values in those tables.

Common values are usually the same column name and data type present in the tables being joined. Those common columns are called the join key or common key.

JOINS can be used in the SELECT, UPDATE, and DELETE statements.

MySQL JOIN Example

In this tutorial, we will use an example database with two tables: customer_list and payments. The goal is to make the concept of joins clear by showing the results of each join type in an example.

• The customer_list table is a list of people we are going to call customers. It contains information about each customer as well as their account balance. Each customer has a unique ID.

• The payments table contains information about each customer's deposits and withdrawals connected to customer IDs.

The common column for these two tables is CustomerID, and we are going to use it as a condition in JOINS.

MySQL JOIN Types

There are several MySQL JOIN types, and each type helps get different results when joining tables:

1. INNER JOIN – Results return matching data from both tables.

2. LEFT OUTER JOIN – Results are from the left table and matching data from the right table.

3. RIGHT OUTER JOIN – Results are from the right table and matching data from the left table.

4. FULL OUTER JOIN – Results are from both tables when there is matching data.

5. CROSS JOIN – Results are a combination of every row from the joined tables.

The following Venn diagrams represent each join type graphically:

Different join types allow users to get results when information is present in only one of the joined tables.

INNER JOIN

The simplest join type is INNER JOIN. The INNER JOIN results with a set of records that satisfy the given condition in joined tables. It matches each row in one table with every row in other tables and allows users to query rows containing columns from both tables.

The syntax for an INNER JOIN is:

SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

The matching_column syntax represents the column common to both tables.

After the SELECT statement, if a column is unique to a table, there is no need to specify the table name.

Since INNER JOIN is considered the default join type, using only the JOIN statement is accepted.

For example:

In this example, table1.column1 is customer_list.CustomerName, while table2.column1 is payments.Deposit. The common column for both tables is CustomerID.

The result-set returned shows the list of customers from the customer_list table and the deposits made by those customers, located in the payments table.

LEFT OUTER JOIN

The LEFT OUTER JOIN (or LEFT JOIN) returns all records from the table on the left side of the join and matching records from the table on the right side of the join. If there are rows for which there are no matching rows on the right-side table, the result value displayed is NULL.

The syntax for LEFT OUTER JOIN is:

SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

For example:

In this example, the left table is customer_list, while the right table is payments. The result set returns all the customers from the customer_list table and the matching results from the payments table.

In places where a customer made no deposit, the returned value displayed is NULL.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN (RIGHT JOIN) is essentially the reverse of LEFT OUTER JOIN.

The RIGHT JOIN returns all records from the table on the right side of the join and matching records from the table on the left side of the join. If there are rows for which there are no matching rows on the left-side table, the result value displayed is NULL.

The syntax for RIGHT OUTER JOIN is:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

For example:

In this example, the results returned show any customers that have made a deposit. The customers who did not make a deposit are not shown in the result.

FULL OUTER JOIN

MySQL does not support FULL JOIN. To get an equivalent result, use a combination of LEFT JOIN, UNION ALL, and RIGHT JOIN, which outputs a union of table 1 and table 2, returning all records from both tables. The columns existing only in one table will be displayed as NULL in the opposite table.

The syntax is:

SELECT * FROM table1
LEFT JOIN table2 ON table1.matching_column = table2.matching_column
UNION ALL
SELECT * FROM table1
RIGHT JOIN table2 ON table1.matching_column = table2.matching_column

For example:

FULL OUTER JOIN does not exclude duplicates, which is why we use UNION ALL to display a union of the two tables, including the duplicates. NULL values are displayed where there is no matching data, i.e., where customers did not make a deposit or withdrawal, or no customer ID is present.

If you want to exclude duplicates from the result set, use the UNION statement instead of UNION ALL to remove duplicate rows:

The result-set includes all matching results, excluding duplicate rows.

CROSS JOIN

The CROSS JOIN (also called CARTESIAN JOIN) joins each row of one table to every row of another table. The CROSS JOIN happens when the matching column or the WHERE condition are not specified. The result-set of a CROSS join is the product of the number of rows of the joined tables.

Use CROSS JOIN when you want a combination of every row from two tables. CROSS JOIN is useful when you want to make a combination of items, for example, colors or sizes.

If the WHERE condition is specified, the CROSS JOIN functions like an INNER JOIN.

The syntax for CROSS JOIN is:

SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1
CROSS JOIN table2;

For example:

In this example, each record from the CustomerName column is joined to each row from the Withdrawal column. It does not make sense to use CROSS JOIN in a database like this, but the example illustrates the result.

Why Are JOINS Useful?

• It's faster. JOINS help retrieve data from two or more related database tables in a single query. JOINS are particularly useful because it is much faster than having to run queries one by one to get the same results.
• MySQL performs better. Another benefit of using JOINS is that MySQL performs better because it uses indexing when performing joins.
• Using JOINS reduces server overhead. You run only a single query, resulting in better and faster performance.

Conclusion

You now know all the different types of MySQL JOINS and how to use them. Feel free to test out different types of joins, as it will be much clearer when you put them to use and see the results for your database example.