MySQL joins have been an issue for many. Here is a simple explanation that demystifies JOIN’s.

INNER JOIN

This is the most used join. This join will return rows from Table 1 that have matching rows in Table 2.

SELECT <columns>
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.column_key = t2.column_key

Another way this can be written is:

SELECT <columns>
FROM table_1 t1
INNER JOIN table_2 t2 USING (column_key)

The above examples are equivalent to:

SELECT <columns>
FROM table_1 t1, table_2 t2
WHERE (t1.column_key = t2.column_key)

 

LEFT JOIN

The left join query will return all rows in the left table (Table 1) irrespective if they exist in the right table (Table 2) along with rows that do match in the right table.

SELECT <columns>
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.column_key=t2.column_key;

 

RIGHT JOIN

The right join query will return all rows in the right table (Table 2) irrespective if they exist in the left table (Table 1) along with rows that do match in the left table.

SELECT <columns>
FROM table_1 t1
RIGHT JOIN table_2 t2 ON t1.column_key=t2.column_key;

 

OUTER JOIN

An outer join is similar to an inner join with the exception that it can also match rows that don’t match.

MySQL does not support FULL OUTER JOIN so we must simulate it. The following query is similar to SQL full outer join query.

SELECT <columns>
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.column_key=t2.column_key
UNION ALL
SELECT <columns>
FROM table_1 t1
RIGHT JOIN table_2 t2 ON t1.column_key=t2.column_key
WHERE t1.column_key IS NULL

 

LEFT EXCLUDE JOIN

The left excluding join will return all rows in the left table (Table 1) that do not match any rows in the right table (Table 2).

SELECT <columns>
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.column_key = t2.column_key
WHERE t2.column_key IS NULL

 

RIGHT EXCLUDE JOIN

The right exclude join will return all rows in the left table (Table 1) that do not match any rows in the right table (Table 2).

SELECT <columns>
FROM table_1 t1
RIGHT JOIN table_2 t2 ON t1.column_key = t2.column_key
WHERE t1.column_key IS NULL;

 

OUTER EXCLUDE JOIN

This will return all rows in the left table (Table 1) and all rows in the right table (Table 2) that don’t match.

SELECT <columns> FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.column_key=t2.column_key
WHERE t2.column_key IS NULL
UNION ALL
SELECT <columns> FROM table_1 t1
RIGHT JOIN table_2 t2 ON t1.column_key=t2.column_key
WHERE t1.column_key IS NULL