This article provides an overview of the natural join in SQL, as well as some basic examples.
What is a Natural Join?
The SQL natural join is a type of equi-join that implicitly combines tables based on columns with the same name and type. The join predicate arises implicitly by comparing all columns in both tables that have the same column names in the joined tables.
The result set contains only one column for each pair of equally named columns. If no columns with the same names are found, the result will be a cross join.
Syntax
A natural join can be applied to any INNER, LEFT, RIGHT, or FULL join. You simply prefix the join type with the NATURAL keyword.
Example of the syntax used on an inner join:
SELECT *
FROM Table1 NATURAL INNER JOIN Table2
ON Table1.Column = Table2.Column;Seeing as INNER is the default value, you can also do it like this:
SELECT *
FROM Table1 NATURAL JOIN Table2
ON Table1.Column = Table2.Column;The NATURAL keyword places an implicit USING clause to the join constraints. It forms a USING list consisting of all column names that appear in both input tables. This obviously only applies to DBMSs that support the USING clause.
Not all DBMSs support natural joins, so check with your DBMS’s documentation.
As I write this, natural joins are supported in PostgreSQL, MySQL, MariaDB, SQLite, and Oracle. However, natural joins are not supported in SQL Server (2019).
Examples
Here are some examples to demonstrate.
Sample Data
First, here are the tables we’ll use for the examples.
The PetTypes table:
+-------------+-----------+ | PetTypeId | PetType | |-------------+-----------| | 1 | Bird | | 2 | Cat | | 3 | Dog | | 4 | Rabbit | +-------------+-----------+ (4 rows affected)
The Pets table:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
The Owners table:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Simpson | (489) 591-0408 | NULL | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Note that:
- The
PetTypeIdcolumn of thePetstable is a foreign key of thePetTypeIdof thePetTypestable (which is the primary key of that table). - The
OwnerIdcolumn of thePetstable is a foreign key of theOwnerIdcolumn of theOwnerstable.
Example 1 – Natural Inner Join
Here’s an example of performing a natural inner join against two of those tables.
SELECT
PetName,
PetType
FROM Pets
NATURAL JOIN PetTypes;Result:
petname | pettype ---------+--------- Fluffy | Cat Fetch | Dog Scratch | Cat Wag | Dog Tweet | Bird Fluffy | Dog Bark | Dog Meow | Cat (8 rows)
In this example, the natural join implicitly joined the tables on the two PetTypeId columns (i.e. the Pets.PetTypeId column, and the PetTypes.PetTypeId column).
This is an implicit way of doing the following:
SELECT
PetName,
PetType
FROM Pets
INNER JOIN PetTypes USING (PetTypeId);Which actually does the following.
SELECT
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;Example 2 – Natural Right Join
Here’s an example of performing a natural right join against two of those tables. This time we have to specify the join type, seeing as we don’t want the (default) inner join.
SELECT
p.PetName,
pt.PetType
FROM Pets p
NATURAL RIGHT JOIN PetTypes pt;Result:
petname | pettype ---------+--------- Fluffy | Cat Fetch | Dog Scratch | Cat Wag | Dog Tweet | Bird Fluffy | Dog Bark | Dog Meow | Cat | Rabbit (9 rows)
In this case, it’s the same as doing the following:
SELECT
p.PetName,
pt.PetType
FROM Pets p
RIGHT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;Example 3 – Natural Full Join on 3 Tables
Here’s an example of performing a natural full join on all three tables.
SELECT
PetName,
PetType,
CONCAT(FirstName, ' ', LastName) AS PetOwner
FROM Owners NATURAL FULL JOIN Pets
NATURAL FULL JOIN PetTypes;Result:
petname | pettype | petowner ---------+---------+---------------- Fluffy | Cat | Nancy Simpson Fetch | Dog | Nancy Simpson Scratch | Cat | Bart Pitt Wag | Dog | Nancy Simpson Tweet | Bird | Homer Connery Fluffy | Dog | Boris Trump Bark | Dog | Bart Pitt Meow | Cat | Boris Trump | | Woody Eastwood | Rabbit | (10 rows)
This time we have a pet owner that doesn’t have a pet, as well as a pet type that’s not assigned to a pet.
Example 4 – Using the Asterisk (*) Wildcard Character
Here’s an example that uses the asterisk (*) wildcard character to select all columns.
SELECT *
FROM Pets
NATURAL JOIN PetTypes;Result:
pettypeid | petid | ownerid | petname | dob | pettype -----------+-------+---------+---------+------------+--------- 2 | 1 | 3 | Fluffy | 2020-11-20 | Cat 3 | 2 | 3 | Fetch | 2019-08-16 | Dog 2 | 3 | 2 | Scratch | 2018-10-01 | Cat 3 | 4 | 3 | Wag | 2020-03-15 | Dog 1 | 5 | 1 | Tweet | 2020-11-28 | Bird 3 | 6 | 4 | Fluffy | 2020-09-17 | Dog 3 | 7 | 2 | Bark | | Dog 2 | 8 | 4 | Meow | | Cat (8 rows)
Note that the pettypeid column is only returned once, even though there are two columns with that name (one in each table). This is how natural joins deal with columns of the same name across tables.
