JOINS

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

JOINS are of five types.

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. CROSS JOIN
5. SELF JOIN

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN

Syntax:

SELECT <column name(s)>
FROM table1
INNER JOIN table2
ON table1.column name = table2.column name;

LEFT JOIN

The LEFT JOIN returns all records from the left table (table1), and the matching records (if any) from the right table (table2).

LEFT JOIN

Syntax:

SELECT <column name(s)>
FROM table1
LEFT JOIN table2
ON table1.column name = table2.column name;

RIGHT JOIN

The RIGHT JOIN returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

RIGHT JOIN

Syntax:

SELECT <column name(s)>
FROM table1
RIGHT JOIN table2
ON table1.column name = table2.column name;

CROSS JOIN

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

CROSS JOIN

Syntax:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

SELF JOIN

A SELF JOIN is a regular join, but the table is joined with itself.

Syntax:

SELECT <column name(s)>
FROM table1 T1, table1 T2
WHERE condition;