How to use a JOIN statement in MySQL

In SQL, the JOIN statement is used to combine row entries of two or more tables that are based on a matching column between them.

Types of JOIN statements

There are four major types of JOINs in SQL:

1. Inner Join: Selects records from Table 1 and Table 2 where the join condition is satisfied.
2. Right Outer Join: Selects all the records from Table 2, and records from Table 1 for which the join condition is satisfied.
3. Left Outer Join: Selects all the records from Table 1, and records from Table 2 for which the join condition is satisfied.
4. Full Outer Join: Selects all the records from Table 1 and Table 2 regardless of whether the join condition is satisfied or not.

Syntax

In the query, we specify the SELECT clause followed by the column(s) to select, then write the FROM clause followed by the Table 1 name. Then comes the JOIN clause followed by the Table 2 name. Join condition appears after that preceded by the ON clause.

SELECT column(s)
FROM table_1
(LFET/RIGHT/INNER) JOIN table_2 
ON join_condition;

Examples

We will be working with the following two tables. The relationship between the two tables is specified by the DeptID key, which is the primary key in the Departments table and the foreign key in the Students table.

JOIN query types

Here are some queries demonstrating different types of JOINs on these tables:

• InnerJoin: This query is selecting rows from both the tables (Students s, Departments d) where the condition s.DeptID = d.DeptID is satisfied.

• LeftJoin: This query is selecting all the rows from the left table (Students s) and only the rows that satisfy the condition s.DeptID = d.DeptID from the right table (Departments d). Notice the last row in the generated result, where the row for stuID = 6 has been generated even though there is no matching entry for s.DeptID = d.DeptID for stuID = 6 when taking the JOIN of these tables.

• RightJoin: This query is selecting all the rows from the right table (Departments d) and only the rows that satisfy the condition s.DeptID = d.DeptID from the left table (Students s). Notice the last row in the generated result, where the row for DeptID = 4 has been generated even though there is no matching entry for s.DeptID = d.DeptID for DeptID = 4 when taking the JOIN of these tables.

• FullJoin: In MySQL, there are no FULL JOINS but we can emulate them, by taking a UNION of both the LEFT JOIN and the RIGHT JOIN. The generated results would essentially include both the extra rows, as were generated in the RIGHT JOIN and LEFT JOIN since we are taking a UNION of both those.