SQL joins are used to combine rows from two or more tables.
There are 4 different types of SQL joins:
- SQL INNER JOIN (or sometimes called simple join)
- SQL LEFT JOIN
- SQL RIGHT JOIN
- SQL FULL JOIN
1. SQL INNER JOIN (simple join)
SQL INNER JOINS return all rows from multiple tables where the join condition is met.
Syntax:
> SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL INNER JOIN returns the shaded area:
The SQL INNER JOIN would return the records where table1 and table2 intersect.
2. SQL LEFT JOIN:
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax:
> SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL LEFT JOIN returns the shaded area:
The SQL LEFT JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
3. SQL RIGHT JOIN:
This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax:
> SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL RIGHT JOIN returns the shaded area:
The SQL RIGHT JOIN would return the all records from table2 and only those records from table1 that intersect with table2.
4. SQL FULL JOIN:
This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
Syntax:
> SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
In this visual diagram, the SQL FULL JOIN returns the shaded area:
The SQL FULL JOIN would return the all records from both table1 and table2.
