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 `JOIN`s 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.

``` sql
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 `JOIN`s 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.


SELECT stuID, First, Last, Name 
FROM Students s
INNER JOIN Departments d
ON s.DeptID = d.DeptID;

InnerJoin

SELECT stuID, First, Last, Name 
FROM Students s
LEFT JOIN Departments d
ON s.DeptID = d.DeptID;

LeftJoin

SELECT stuID, First, Last, Name 
FROM Students s
RIGHT JOIN Departments d
ON s.DeptID = d.DeptID;

RightJoin

SELECT stuID, First, Last, Name 
FROM Students s
LEFT JOIN Departments d
ON s.DeptID = d.DeptID

UNION

SELECT stuID, First, Last, Name 
FROM Students s
RIGHT JOIN Departments d
ON s.DeptID = d.DeptID;

FullJoin

How to use a JOIN statement in MySQL

Combines rows from tables based on matching columns: Inner Join, Left Join, Right Join, Full Join. Syntax: SELECT columns FROM table1 JOIN table2 ON condition.

How to use a JOIN statement in MySQL

Types of JOIN statements

Syntax

Examples

JOIN query types

Types of `JOIN` statements

`JOIN` query types