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.
- MySQL Server and MySQL shell installed
- A MySQL user account with root privileges
What Are JOINS in MySQL?
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
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.
Note: Learn how to create a table in MySQL.
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.
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;
matching_column syntax represents the column common to both tables.
SELECT statement, if a column is unique to a table, there is no need to specify the table name.
INNER JOIN is considered the default join type, using only the
JOIN statement is accepted.
In this example,
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.
Note: See how to find duplicate values in a table in different ways, including
LEFT OUTER JOIN
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;
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
RIGHT OUTER JOIN (
RIGHT JOIN) is essentially the reverse of
LEFT OUTER JOIN.
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;
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
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
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 (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.
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.
WHERE condition is specified, the
CROSS JOIN functions like an
The syntax for
CROSS JOIN is:
SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 CROSS JOIN table2;
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.
Note: Read our article to learn how to create an index in MySQL.
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.