In relational databases, such as Oracle, MYSQL Server, SQL and various others, the data is stored in numerous tables that are connected to one another with the same key value. As a result, there is an ongoing requirement to extract data from two or more tables into a result table based on certain criteria. This is achieved in MySQL Server using the MySQL JOIN Clause. In this tutorial on MySQL JOINS, we will discuss various types of JOINS used in MySQL with syntax and examples.
Also Read – SQL Outer Join: LEFT, RIGHT & FULL With Examples
What are JOINS in MySQL?
JOINS is a MySQL Clause that combines records from various tables or retrieves data from these tables based on the availability of a shared column between them. The MySQL clause allows you to fetch and integrate records from two or more database tables. In short, JOINS specify how MySQL Server will use data from a single table to pick entries from another.
Since now you know what JOINS are, let’s take a look at different types of JOINS.
Types of JOINS in MySQL
There are mainly six types of JOINS in MySQL.
- INNER JOIN
- LEFT JOIN(or LEFT OUTER JOIN)
- RIGHT JOIN(or RIGHT OUTER JOIN)
- FULL JOIN(or FULL OUTER JOIN)
- SELF JOIN
- CROSS JOIN
Consider the two tables shown below for employees and departments:
We will use them for our examples.
1. INNER JOIN
Inner join is a type of relational database join operation that returns those records which has common values in both tables. The result from this includes only those rows from tables that fulfil the specified join condition, i.e., the value of the common field is the same.
Syntax:
SELECT table01.cols1,table01.cols2,table02.cols1,...
FROM table01
INNER JOIN table02
ON table01.match_cols = table02.match_cols;
table01: First table.
table02: Second table
match_cols: Common column to both the tables.Code language: SQL (Structured Query Language) (sql)Note: JOIN is the same as INNER JOIN; we can use any of these clauses.
Example:
This query will show the list of employees with their department names.
SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
FROM employeees e
INNER JOIN employeees m ON e.manager_id = m.employee_id;Code language: SQL (Structured Query Language) (sql)
2. LEFT JOIN
Left join is a type of relational database join operation that returns the left side of the join and matches those records that have common values in both tables. For the rows for which there are no common values in the right table, the result set will contain a null value. LEFT JOIN is also termed as LEFT OUTER JOIN.
Syntax:
SELECT table01.cols1,table01.cols2,table02.cols1,...
FROM table01
LEFT JOIN table02
ON table01.match_cols = table02.match_cols;
table01: First table.
table02: Second table
match_cols: Common column to both the tables.Code language: SQL (Structured Query Language) (sql)Note: LEFT JOIN is the same as LEFT OUTER JOIN; we can use any of these clauses.
Example:
SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
FROM employeees e
LEFT JOIN employeees m ON e.manager_id = m.employee_id;Code language: SQL (Structured Query Language) (sql)
3. RIGHT JOIN
Right join is a type of relational database join operation that returns the right side of the join and matches those records that have common values in both tables. For the rows for which there are no common values in the left table, the result set will contain a null value. RIGHT JOIN is also termed as RIGHT OUTER JOIN.
Syntax:
SELECT table01.cols1,table01.cols2,table02.cols1,...
FROM table01
RIGHT JOIN table02
ON table01.match_cols = table02.match_cols;
table01: First table.
table02: Second table
match_cols: Common column to both the tables.Code language: SQL (Structured Query Language) (sql)Note: RIGHT JOIN is the same as RIGHT OUTER JOIN; we can use any of these clauses.
Example:
SELECT m.employee_id AS manager_id, m.employee_name AS manager_name, e.employee_id, e.employee_name
FROM employeees e
RIGHT JOIN employeees m ON e.manager_id = m.employee_id;Code language: SQL (Structured Query Language) (sql)
4. FULL JOIN
A full join is a type of relational database join operation that returns all the records of the right table of the join and of the left table. It combines and returns, as a result set table. It is used for creating a large database. FULL JOIN is also termed as FULL OUTER JOIN.
Syntax:
SELECT * FROM table01
LEFT JOIN table02 ON table01.column = table02.column
UNION
SELECT * FROM table01
RIGHT JOIN table02 ON table01.column = table02.columnCode language: SQL (Structured Query Language) (sql)Note: We don’t have FULL JOIN in MySQL.So, instead, we will use a combination of LEFT and RIGHT JOIN and the UNION query to emulate the same.
Example:
SELECT *
FROM employeees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT *
FROM employeees
RIGHT JOIN departments d ON employeees.department_id = d.department_id
WHERE employeees.department_id IS NULL;Code language: SQL (Structured Query Language) (sql)
5. SELF JOIN
Self-join is a type of relational database join operation that joins the table with itself and matches those records that have common values in both tables. In a Self-join, records from the same table are matched based on some unique values in different columns within the same table.
Syntax:
SELECT t1.cols1, t1.cols2, ..., t2.cols1, t2.cols2, ...
FROM table_name t1
JOIN table_name t2 ON t1.related_cols = t2.related_cols;
table_name: Name of the table you want to join.
t1,t2: Aliases for the table_name.
related_cols: Columns that you use ,to join to itself.Code language: SQL (Structured Query Language) (sql)Example:
SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
FROM employeees e
LEFT JOIN employeees m ON e.manager_id = m.employee_id;Code language: SQL (Structured Query Language) (sql)
6. CROSS JOIN
Cross join is a type of relational database join operation that joins each row from the first table with every row from the second table, turning out as a Cartesian product of the two tables. CROSS JOIN is also termed as CARTESIAN JOIN.
Syntax:
SELECT table01.cols1, table01.cols2, table02.cols1, ...
FROM table01
CROSS JOIN table02;
table01: First table.
table02: Second tableCode language: SQL (Structured Query Language) (sql)Example:
SELECT e.employee_id AS employee_id, e.employee_name AS employee_name, m.employee_id AS manager_id, m.employee_name AS manager_name
FROM employeees e
CROSS JOIN employeees m;Code language: SQL (Structured Query Language) (sql)
Read More – How To Convert SQL Subquery To Join?
Conclusion
In this tutorial, we have learned what is the MySQL JOINS and their types and how to use INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN and OUTER JOIN. Each join type serves a specific purpose and can be utilized as per requirements and connections between tables.