Understanding MySQL Joins

This is the 3rd post in a series on MySQL performance.

An SQL query walks into a bar and sees two tables. He walks up to them and asks “Can I join you?”

— Source: Unknown

The join operation transforms data from a normalized model to a denormalized form that is suitable for a specific purpose. Because it combines scattered data fragments, joining is particularly sensitive to disk seek latencies. Indexing is the best way to reduce response times.

Data from several tables are combined in a single query by using JOINS. JOINS aggregate data from several tables and return it as a single result. The tables that are joined contain unique or common data, and each is connected logically. A join is used to retrieve data from tables that have a common field.

The SELECT, UPDATE and DELETE commands can all use JOINS.

JOINS

A MySQL JOIN type specifies how tables in a query are linked. MySQL supports three types of JOIN clauses: INNER JOIN, OUTER JOIN, and CROSS JOIN. The LEFT JOIN and the RIGHT JOIN are two types of OUTER JOINS.

To demonstrate how JOINS work, we will create a schema and insert some data into it.

CREATE TABLE orders (
order_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY(order_id)
)

CREATE TABLE customers (
customer_id INT NOT NULL,
customer_name VARCHAR(50),
country VARCHAR(10),
PRIMARY KEY(customer_id)
);

INNER JOIN

By using INNER JOINS, only common matched records are retrieved. The INNER JOIN clause restricts records retrieval from table customers and table orders to those that satisfy the join requirement. It is the most commonly used JOIN type.

Venn Diagram of INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers INNER JOIN orders on customers.customer_id= orders.customer_id;

OUTER JOIN

In contrast to INNER JOINS, OUTER JOINS yield both non-matching and matching records. When rows in a connected table don’t match, NULL values will be displayed. There are two types of outer joins in MySQL.

• LEFT JOIN
• RIGHT JOIN

Unlike SQL Server, MySQL does not have a distinct JOIN type for FULL OUTER JOIN. However, you can combine LEFT OUTER JOIN with RIGHT OUTER JOIN for an effect similar to that of FULL OUTER JOIN.

LEFT JOIN

You can use the LEFT JOIN to get all entries from table customers and those from table orders that meet the criteria for the join. If a record in a table does not fit the criteria, NULL values are displayed. The LEFT JOIN returns all records from the left table (customers) even if there is no match from the right table (orders).

Venn Diagram of LEFT JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id;

LEFT JOIN EXCLUDING INNER JOIN

It is a special case where the join query returns all the records from the left table (customers) that do not match any records from the right table (orders).

Venn Diagram of LEFT JOIN EXCLUDING INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id where orders.customer_id IS NULL;

RIGHT JOIN

When using RIGHT JOINS, you will be able to see all the entries from right table customers and from left table orders that meet the join criteria. Records from the right table customers that do not meet the criteria are presented with NULL values. The RIGHT JOIN returns all the records from the right table (customers) even if there are no matches in the left table (orders).

Venn Diagram of RIGHT JOIN

SELECT  customers.customer_id, customer_name, country, order_id from orders RIGHT JOIN customers on orders.customer_id= customers.customer_id;

RIGHT JOIN EXCLUDING INNER JOIN

It is a special case where the join query returns all the records from the right table (customers) that do not match any of the records in the left table (orders).

Venn Diagram of RIGHT JOIN EXCLUDING INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from orders RIGHT JOIN customers on orders.customer_id= customers.customer_id where orders.customer_id IS NULL;

FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN

There is no separate JOIN type for FULL OUTER JOIN in MySQL, but we can combine LEFT JOIN and RIGHT JOIN to achieve the same effect as FULL OUTER JOIN.

Venn Diagram of FULL OUTER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id
UNION
SELECT  customers.customer_id, customer_name, country, order_id from customers RIGHT JOIN orders on customers.customer_id= orders.customer_id;

CROSS JOIN

CROSS JOINS, commonly known as cartesian joins, return all possible table-row combinations. In the absence of additional conditions, the result set is obtained by multiplying each row of table customers by all rows of table orders. CROSS JOIN results might be quite big, here only a few results are shown from a total of 120 rows.

Venn Diagram of CROSS JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers CROSS JOIN orders;

In most cases, columns that are commonly used for searching or joining should be indexed.