Explains the different types of join operations that can be performed using Structured Query Language (SQL).

Different Types Of SQL Joins
May 03, 2019
SQL Joins

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.

Write a Comment

Click on the captcha image to get new code.
Discussion Forum by DISQUS