How to Use the JOIN Clause in MySQL

08/04/2021

Contents

In this article, you will learn how to use the JOIN clause in MySQL.

Using the JOIN clause in MySQL

The JOIN clause in MySQL is used to combine rows from two or more tables based on a related column between them.

Types of JOINs

MySQL supports four types of JOINs:

  • INNER JOIN: Returns only the rows that have matching values in both tables.
  • LEFT JOIN: Returns all the rows from the left table and the matching rows from the right table.
  • RIGHT JOIN: Returns all the rows from the right table and the matching rows from the left table.
  • FULL OUTER JOIN: Returns all the rows from both tables, including the ones that don’t have matching values.

Syntax

The basic syntax of the JOIN clause in MySQL is as follows:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

In this syntax, “table1” and “table2” are the names of the tables to be joined, and “column_name” is the name of the column used to match the rows in both tables. The result set will include the columns specified in the SELECT statement from both tables.

Examples

INNER JOIN

Suppose we have two tables called “customers” and “orders”. The “customers” table has columns “id”, “name”, and “email”, while the “orders” table has columns “id”, “customer_id”, and “order_date”. We want to retrieve the customer name and order date for all orders.

SELECT customers.name, orders.order_date
FROM customers
JOIN orders
ON customers.id = orders.customer_id;

In this example, we are using an INNER JOIN to combine rows from the “customers” and “orders” tables based on the “id” column from “customers” and the “customer_id” column from “orders”. The result set will include the customer name and order date for all orders.

LEFT JOIN

Suppose we have two tables called “students” and “grades”. The “students” table has columns “id”, “name”, and “major”, while the “grades” table has columns “id”, “student_id”, and “grade”. We want to retrieve the student name and grade for all students, even if they don’t have a grade yet.

SELECT students.name, grades.grade
FROM students
LEFT JOIN grades
ON students.id = grades.student_id;

In this example, we are using a LEFT JOIN to combine rows from the “students” and “grades” tables based on the “id” column from “students” and the “student_id” column from “grades”. The result set will include the student name and grade, but if a student doesn’t have a grade yet, the grade value will be NULL.

FULL OUTER JOIN

Suppose we have two tables called “employees” and “departments”. The “employees” table has columns “id”, “name”, “salary”, and “department_id”, while the “departments” table has columns “id” and “name”. We want to retrieve the employee name, salary, and department name for all employees, including the ones who don’t have a department yet.

SELECT employees.name, employees.salary, departments.name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

In this example, we are using a FULL OUTER JOIN to combine rows from the “employees” and “departments” tables based on the “department_id” column from “employees” and the “id” column from “departments”. The result set will include the employee name, salary, and department name for all employees, including the ones who don’t have a department yet. If an employee doesn’t have a department yet, the department name value will be NULL.

Using aliases

We can use aliases to simplify the syntax of the JOIN clause in MySQL. Here’s an example:

SELECT c.name, o.order_date
FROM customers c
JOIN orders o
ON c.id = o.customer_id;

In this example, we are using aliases “c” and “o” for the “customers” and “orders” tables, respectively. This allows us to refer to the tables by their aliases instead of their full names in the SELECT and ON clauses.

Joining more than two tables

We can join more than two tables in MySQL by using multiple JOIN clauses. Here’s an example:

SELECT c.name, o.order_date, p.product_name
FROM customers c
JOIN orders o
ON c.id = o.customer_id
JOIN order_items oi
ON o.id = oi.order_id
JOIN products p
ON oi.product_id = p.id;

In this example, we are joining four tables: “customers”, “orders”, “order_items”, and “products”. We are using three JOIN clauses to combine the rows based on the “id” and “order_id” columns.

#MySQL