This post explains the different types of Joins that can be performed in Structured Query Language (SQL).
Notes: Based on the feedback received for this post, using Venn Diagrams to explain SQL JOINs should be considered only a reference since these do not cover all the possible cases for some of the Joins. The Venn Diagrams are being considered as more appropriate to explain the usage of Set Operations using SQL.
Base Tables
Person Table
| person_id | person_name | person_age |
|---|---|---|
| 1 | Lawrence | 32 |
| 2 | Flora | 22 |
Interest Table
| interest_id | person_id | interest_title |
|---|---|---|
| 1 | 1 | Books |
| 2 | 1 | Computers |
| 3 | 2 | Writing |
| 4 | 2 | Movies |
CROSS JOIN
CROSS JOIN also called as Cartesian JOIN simply returns all the rows from both the tables without any matching conditions i.e. it simply pairs each row of the first table with each row of the second table without using WHERE clause.
Suppose Table A consist of n rows and Table B consist of m rows, then the end result will show all the selected columns from both the tables having n x m rows.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
CROSS JOIN <table_b>
In most of the cases, it's useful only when the WHERE clause is used to filter the end results.
Query - SELECT * FROM person CROSS JOIN interest
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 2 | Flora | 22 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 2 | 1 | Computers |
| 1 | Lawrence | 32 | 3 | 2 | Writing |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 1 | Lawrence | 32 | 4 | 2 | Movies |
| 2 | Flora | 22 | 4 | 2 | Movies |
INNER JOIN
INNER JOIN selects only the rows having common matched values in both the tables leaving all the other rows without any match.
It works by matching the join conditions in both the tables, resulting in selected columns from both the tables having matched rows. The rows without satisfying the join predicates will be excluded from both the tables. The rows having a null value for the columns specified in the join-predicates will also be excluded.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
INNER JOIN <table_b> ON
<table_a.match> = <table_b.match>
Optional WHERE clause having appropriate and required conditions can be used to further filter the end results.
Query - SELECT * FROM person INNER JOIN interest ON person.person_id=interest.person_id
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 2 | Flora | 22 | 4 | 2 | Movies |
Equi-JOIN
Equi-JOIN explicitly specifies the matching columns by explicitly using the equality operator i.e. =.
The INNER JOIN can be considered as Equi-JOIN if it only consists of join-predicates having equality operator i.e. =, else the INNER JOIN can be considered as Nonequi-JOIN having other operators including lesser than i.e. <, greater than i.e. >.
Similar to INNER JOIN, Equi-JOIN can also be LEFT OUTER JOIN or RIGHT OUTER JOIN.
We can write the query for INNER JOIN using the keyword JOIN with join-predicates using equal(=) as shown below.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
JOIN <table_b> ON
<table_a.match> = <table_b.match>
We can also write the query for INNER JOIN using WHERE clause using equal(=) as shown below.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>, <table_b>
WHERE <table_a.match> = <table_b.match>
The example using the tables person and interest is as shown below.
Query - SELECT * FROM person JOIN interest ON person.person_id=interest.person_id
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 2 | Flora | 22 | 4 | 2 | Movies |
NATURAL JOIN
NATURAL JOIN is Equi-JOIN without specifying any join-predicate or conditions.
The NATURAL JOIN results in rows having selected columns from both the tables by matching the values from columns having the same name from both the tables. It considers all the columns from both the tables having the same name.
It also excludes duplicate columns in the end result.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
NATURAL JOIN <table_b>
The example using the tables person and interest is as shown below.
Query - SELECT * FROM person NATURAL JOIN interest
Results
| person_id | person_name | person_age | interest_id | interest_title |
|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | Books |
| 1 | Lawrence | 32 | 2 | Computers |
| 2 | Flora | 22 | 3 | Writing |
| 2 | Flora | 22 | 4 | Movies |
Base Tables Updated
To understand the outer joins, the base tables are updated as shown below.
Person Table
| person_id | person_name | person_age |
|---|---|---|
| 1 | Lawrence | 32 |
| 2 | Flora | 22 |
| 3 | Steve | 26 |
| 4 | Michael | 18 |
Interest Table
| interest_id | person_id | interest_title |
|---|---|---|
| 1 | 1 | Books |
| 2 | 1 | Computers |
| 3 | 2 | Writing |
| 4 | 2 | Movies |
| 5 | NULL | Music |
LEFT JOIN
LEFT JOIN also called as LEFT OUTER JOIN returns the rows having selected columns from both the tables having all the rows from the left table and only matching rows from the right table using the given join-predicates.
It also includes the columns having NULL values from the left table.
The resulting rows having no match on the right table will have a NULL value assigned to the selected columns from the right table.
It assures that the end results will have all the rows from the left table with or without matching rows from the right table.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
LEFT JOIN <table_b> ON
<table_a.match> = <table_b.match>
The LEFT JOIN example using the updated tables person and interest is as shown below.
Query - SELECT * FROM person LEFT JOIN interest ON person.person_id=interest.person_id
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 2 | Flora | 22 | 4 | 2 | Movies |
| 3 | Steve | 26 | NULL | NULL | NULL |
| 4 | Michael | 18 | NULL | NULL | NULL |
We can see that the columns from the right table are filled with NULL values where person_id from the left table does not match with person_id from the right table, but all the rows from the left table are included in the end results.
LEFT JOIN Without Intersection
LEFT JOIN Without Intersection is a special case of LEFT JOIN where the end results exclude all the matching rows from both the tables.
The resulting rows consist of only the rows from the left table having no match on the right table with a NULL value assigned to the selected columns from the right table.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
LEFT JOIN <table_b> ON
<table_a.match> = <table_b.match>
WHERE <table_b.match> IS NULL
The LEFT JOIN without intersection using the tables person and interest is as shown below.
Query - SELECT * FROM person LEFT JOIN interest ON person.person_id=interest.person_id WHERE interest.person_id IS NULL
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 3 | Steve | 26 | NULL | NULL | NULL |
| 4 | Michael | 18 | NULL | NULL | NULL |
RIGHT JOIN
RIGHT JOIN also called as RIGHT OUTER JOIN is similar to LEFT JOIN with reversed selection. It returns the rows having selected columns from both the tables having all the rows from the right table and only matching rows from the left table using the given join-predicates.
It also includes the columns having NULL values from the right table.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
RIGHT JOIN <table_b> ON
<table_a.match> = <table_b.match>
RIGHT JOIN example using the tables person and interest is as shown below.
Query - SELECT * FROM person RIGHT JOIN interest ON person.person_id=interest.person_id
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 2 | Flora | 22 | 4 | 2 | Movies |
| NULL | NULL | NULL | 5 | NULL | Music |
We can see that the columns from the left table are filled with NULL values where person_id from the left table does not match with person_id from the right table, but all the rows from the right table are included in the end results.
RIGHT JOIN Without Intersection
RIGHT JOIN Without Intersection is a special case of RIGHT JOIN where the end results exclude all the matching rows from both the tables.
The resulting rows consist of only the rows from the right table having no match on the left table with a NULL value assigned to the selected columns from the left table.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
RIGHT JOIN <table_b> ON
<table_a.match> = <table_b.match>
WHERE <table_a.match> IS NULL
An example of RIGHT JOIN without intersection using the tables person and interest is as shown below.
Query - SELECT * FROM person RIGHT JOIN interest ON person.person_id=interest.person_id WHERE person.person_id IS NULL
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| NULL | NULL | NULL | 5 | NULL | Music |
FULL JOIN
FULL JOIN also called as FULL OUTER JOIN returns the rows having selected columns from both the tables having all the matched rows from both the tables with the rows without any match from both the tables by filling the columns values of the other table by NULL as listed below.
- Returns rows having selected columns with rows from both the tables having matched values.
- Returns rows having selected columns with rows from the left table without any match in the right table and right table columns having null values.
- Returns rows having selected columns with rows from the right table without any match in the left table and left table columns having null values.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
FULL JOIN <table_b> ON
<table_a.match> = <table_b.match>
Notes: MySQL among the standard databases does not support FULL JOIN.
FULL JOIN example using the tables person and interest is as shown below.
Query - SELECT * FROM person FULL JOIN interest ON person.person_id=interest.person_id
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 1 | Lawrence | 32 | 1 | 1 | Books |
| 1 | Lawrence | 32 | 2 | 1 | Computers |
| 2 | Flora | 22 | 3 | 2 | Writing |
| 2 | Flora | 22 | 4 | 2 | Movies |
| 3 | Steve | 26 | NULL | NULL | NULL |
| 4 | Michael | 18 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | NULL | Music |
FULL JOIN Without Intersection
FULL JOIN Without Intersection is a special case of FULL JOIN where matched rows from both the joining tables will be excluded from the end results.
- Excludes rows having selected columns with rows from both the tables having matched values.
- Returns rows having selected columns with rows from the left table without any match in the right table and right table columns having null values.
- Returns rows having selected columns with rows from the right table without any match in the left table and left table columns having null values.
SELECT <table_a.column_1, table_b.column_2, ...>
FROM <table_a>
FULL JOIN <table_b> ON
<table_a.match> = <table_b.match>
WHERE <table_a.match> IS NULL
OR <table_b.match> IS NULL
FULL JOIN without intersection example using the tables person and interest is as shown below.
Query - SELECT * FROM person FULL JOIN interest ON person.person_id=interest.person_id WHERE person.person_id IS NULL OR interest.person_id IS NULL
Results
| person_id | person_name | person_age | interest_id | person_id | interest_title |
|---|---|---|---|---|---|
| 3 | Steve | 26 | NULL | NULL | NULL |
| 4 | Michael | 18 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | NULL | Music |
Base Tables Updated
To understand SELF JOIN, the base table person is updated as shown below.
Person Table
| person_id | person_name | person_age | person_manager_id |
|---|---|---|---|
| 1 | Lawrence | 32 | NULL |
| 2 | Flora | 22 | NULL |
| 3 | Steve | 26 | 1 |
| 4 | Michael | 18 | 1 |
Self JOIN
Self JOIN is a special case of INNER JOIN where the join-predicate consist of the same table as shown below. It can generate useful results with the appropriate WHERE clause.
SELECT <a.column_1, b.column_2, ...>
FROM <table_a> AS <a>
INNER JOIN <table_a> AS <b> ON
<a.match> = <b.match>
WHERE <a.column_1> NOT NULL AND <b.column_2> NOT NULL
Self JOIN example using the table person is as shown below.
Query - SELECT p1.person_id AS id, p1.person_name AS manager, p2.person_name AS name FROM person AS p1 INNER JOIN person AS p2 ON p1.person_id=p2.person_manager_id WHERE p2.person_manager_id > 0
Results
| id | manager | name |
|---|---|---|
| 1 | Lawrence | Steve |
| 1 | Lawrence | Michael |
In this post, we have discussed the commonly used SQL joins with practical examples. You can write your views about SQL joins by submitting a comment.
