Joins in MySQL

Summary: In this tutorial, we will learn what are Joins in MySQL, what are the different types of MySQL Joins and how and when to use different joins in MySQL.

Introduction to MySQL Joins

Joins in MySQL are used to retrieve data from two or more related tables at a time.

In general, tables are related to each other using foreign key constraint.

For example, consider the following two tables:

Both the tables are related to each other by tblEmp.dept_id = tblDept.id.

We can join the data of both the tables (i.e. fetch from both tables) using a single MySQL query as follows:

SELECT name, salary, dept_name FROM tblEmp
JOIN tblDept
ON tblEmp.dept_id = tblDept.id

This is an example of one of the types of joins available in MySQL i.e. inner join.

Different Types of MySQL Joins

MySQL in general supports the following 3 types of Joins:

1. INNER JOIN
2. OUTER JOIN
3. CROSS JOIN

Let’s discuss each types with examples.

INNER JOIN

Inner Join in MySQL returns only the matching rows between the table. The non-matching rows are eliminated.

The general syntax for INNER JOIN in MySQL is:

SELECT column_list
FROM table_1
[INNER] JOIN table_2 
ON table_1.column = table_2.column;

where syntax within square brackets is optional.

Example:

SELECT name, salary, dept_name FROM tblEmp
JOIN tblDept
ON tblEmp.dept_id = tblDept.id;

Notice the row entry with field name as ‘Jack’ is not a part of the join result.

It is because dept_id entry of that row is null as a result, didn’t match any entry in the empDept table.

INNER JOIN  is semantically equivalent to the CROSS JOIN in the absence of a join condition.

dev.mysql.com

OUTER JOIN

Outer Joins in MySQL is of two types:

• LEFT JOIN or LEFT OUTER JOIN
• RIGHT JOIN or RIGHT OUTER JOIN

LEFT JOIN

Left Join in MySQL returns returns all the matching rows + non matching rows from the left table.

The general syntax for Left Join in MySQL is:

SELECT column_list
FROM table_1
LEFT [OUTER] JOIN table_2 
ON table_1.column = table_2.column;

where syntax within square brackets is optional.

Example:

SELECT name, salary, dept_name FROM tblEmp
LEFT JOIN tblDept
ON tblEmp.dept_id = tbl.Dept.id;

Notice, every entry of the left table i.e. tblEmp is fetched along with its corresponding mentioned columns.

For an entry not having any match in the right table, null is returned for the right table fields.

RIGHT JOIN

Right Join in MySQL returns all the matching + non matching rows from the right table.

The general syntax for the Right Join in MySQL is:

SELECT column_list
FROM table_1
RIGHT [OUTER] JOIN table_2 
ON table_1.column = table_2.column;

Example:

SELECT name, salary, dept_name FROM tblEmp
RIGHT JOIN tblDept
ON tblEmp.dept_id = tblDept.id;

The output result has dept_name column fetched from the right table i.e. tblDept. It contains all the entries, irrespective of the fact whether the left table has corresponding match or not.

For a non matched row, null is returned for the left table fields.

Note: MySQL does not support FULL OUTER JOIN.

CROSS JOIN

CROSS Join in MySQL produces the Cartesian product of the two tables involved in the join (that is, each and every row in the first table is joined to each and every row in the second table).

The general syntax for Cross Join in MySQL is:

SELECT column_list
FROM table_1
CROSS JOIN table_2;

The simple SELECT STATMENT with multiple tables separated with comma also behaves semantically as a CROSS JOIN statement:

SELECT column_list
FROM table_1, table_2;

Example:

The use of condition in the CROSS JOIN SQL statement is redundant because it anyways matches each and every row of the first table with each and every row of the second table.

In this tutorial, we learned what are Joins in MySQL, different types of Joins in MySQL along with their syntax and examples.