The JOIN clause
The JOIN clause combines data from two tables according to the rules of the specified JOIN type. The general JOIN syntax is:
<table1> <JOIN_type> <table2> [ <join_condition> ]The syntax of JOIN clauses that process string matches is:
<table1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2> ON <boolean_expression>
<table1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2> USING ( <join_column_list> )
<table1> NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2>INNER and OUTER are optional words in all forms. The default value is INNER. However, if the LEFT, RIGHT, or FULL modifier is specified without INNER or OUTER, PostgreSQL executes the JOIN clause as OUTER JOIN.
The ON and USING clause specifies the JOIN condition. Also, the NATURAL keyword implicitly sets the JOIN condition. The condition determines, which rows of the two source tables corresponds to each other.
To see how different types of JOIN operate with data, create two tables:
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR (100),
cover_id INT
);
INSERT INTO books (title, cover_id) VALUES
('Hyperion', 1),
('1984', 2),
('War and Peace', 3),
('The Time Machine',3),
('Mrs. Dalloway',5 );
CREATE TABLE covers (
id SERIAL PRIMARY KEY,
name VARCHAR (50)
);
INSERT INTO covers (name) VALUES
('Paperback'),
('Hardcover'),
('Hardcover with dust jacket'),
('Leatherette cover');The books table contains the cover_id field. This field is a foreign key that refers to the primary key in the covers table and serves for defining relationships between tables.
CROSS JOIN
CROSS JOIN resulted table contains all possible combinations of rows from table1 and table2 (the Cartesian product). The set of the joined table columns includes all table1 columns, followed by all table2 columns. If the tables have N and M rows respectively, the joined table has N * M rows. CROSS JOIN is equivalent to FROM table1 INNER JOIN table2 ON TRUE.
Example:
SELECT * FROM books CROSS JOIN covers;The result:
id | title | cover_id | id | name ----+------------------+----------+----+---------------------------- 1 | Hyperion | 1 | 1 | Paperback 2 | 1984 | 2 | 1 | Paperback 3 | War and Peace | 3 | 1 | Paperback 4 | The Time Machine | 3 | 1 | Paperback 5 | Mrs. Dalloway | 5 | 1 | Paperback 1 | Hyperion | 1 | 2 | Hardcover 2 | 1984 | 2 | 2 | Hardcover 3 | War and Peace | 3 | 2 | Hardcover 4 | The Time Machine | 3 | 2 | Hardcover 5 | Mrs. Dalloway | 5 | 2 | Hardcover 1 | Hyperion | 1 | 3 | Hardcover with dust jacket 2 | 1984 | 2 | 3 | Hardcover with dust jacket 3 | War and Peace | 3 | 3 | Hardcover with dust jacket 4 | The Time Machine | 3 | 3 | Hardcover with dust jacket 5 | Mrs. Dalloway | 5 | 3 | Hardcover with dust jacket 1 | Hyperion | 1 | 4 | Leatherette cover 2 | 1984 | 2 | 4 | Leatherette cover 3 | War and Peace | 3 | 4 | Leatherette cover 4 | The Time Machine | 3 | 4 | Leatherette cover 5 | Mrs. Dalloway | 5 | 4 | Leatherette cover (20 rows)
INNER JOIN
The INNER JOIN examines each row in table1 and checks if table2 contains a row that satisfies the JOIN condition. If a corresponding row is found, the INNER JOIN creates a new row that contains columns from both tables and adds it to the result set.
Example:
SELECT books.id, books.title, covers.name cover FROM books
INNER JOIN covers ON books.cover_id = covers.id;The result contains book’s id, title, and cover type. The Mrs. Dalloway book is not included in the result since for its cover_id value (4) a corresponding row in covers is not found:
id | title | cover ----+------------------+---------------------------- 1 | Hyperion | Paperback 2 | 1984 | Hardcover 3 | War and Peace | Hardcover with dust jacket 4 | The Time Machine | Hardcover with dust jacket
LEFT OUTER JOIN
The LEFT OUTER JOIN clause performs the INNER JOIN operation first. After that, each table1 row, which is excluded from the INNER JOIN result, is added to the LEFT OUTER JOIN result with null values in the table2 columns. The joined table always has at least one row for each table1 row.
Example:
SELECT books.id, books.title, covers.name cover FROM books
LEFT OUTER JOIN covers ON books.cover_id = covers.id;The result contains a row with the Mrs. Dalloway book, in which the covers.name column with the cover alias has the null value:
id | title | cover ----+------------------+---------------------------- 1 | Hyperion | Paperback 2 | 1984 | Hardcover 3 | War and Peace | Hardcover with dust jacket 4 | The Time Machine | Hardcover with dust jacket 5 | Mrs. Dalloway |
RIGHT OUTER JOIN
The RIGHT OUTER JOIN clause performs the INNER JOIN operation first. After that, each table2 row, which is excluded from the INNER JOIN result, is added to the RIGHT OUTER JOIN result with null values in the table1 columns. The joined table always has one row for each table2 row.
Example:
SELECT books.id, books.title, covers.name cover FROM books
RIGHT OUTER JOIN covers ON books.cover_id = covers.id;The result contains a row with Leatherette cover, in which the books.id and books.title columns have null values:
id | title | cover
----+------------------+----------------------------
1 | Hyperion | Paperback
2 | 1984 | Hardcover
3 | War and Peace | Hardcover with dust jacket
4 | The Time Machine | Hardcover with dust jacket
| | Leatherette cover
FULL OUTER JOIN
The FULL OUTER JOIN clause performs the INNER JOIN operation first. After that, each table1 row, which is excluded from the INNER JOIN result, is added to the FULL OUTER JOIN result with null values in the table2 columns. Then each table2 row, which is excluded from the INNER JOIN result, is added to the FULL OUTER JOIN result with null values in the table1 columns.
Example:
SELECT books.id, books.title, covers.name cover FROM books
FULL OUTER JOIN covers ON books.cover_id = covers.id;The result:
id | title | cover
----+------------------+----------------------------
1 | Hyperion | Paperback
2 | 1984 | Hardcover
3 | War and Peace | Hardcover with dust jacket
4 | The Time Machine | Hardcover with dust jacket
5 | Mrs. Dalloway |
| | Leatherette cover
JOIN conditions
The JOIN condition determines, which rows from two source tables
match. You can use the ON, USING, or NATURAL clause to specify the condition.
ON
The ON clause returns a boolean value. A row from table1 matches a row from table2 if the ON expression evaluates to true.
Create two tables for test examples:
CREATE TABLE table1(
num INT PRIMARY KEY,
name VARCHAR (25)
);
INSERT INTO table1 (num, name) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
CREATE TABLE table2(
num INT PRIMARY KEY,
value VARCHAR (25)
);
INSERT INTO table2 (num, value) VALUES
(1, 'value1'),
(3, 'value2'),
(5, 'value3');table1:
num | name -----+------ 1 | a 2 | b 3 | c
table2:
num | value -----+------- 1 | value1 3 | value2 5 | value3
Execute the following query with the ON condition:
SELECT * FROM table1 INNER JOIN table2 ON table1.num = table2.num;The result:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1 3 | c | 3 | value2
The JOIN condition can also contain additional clauses that do not relate directly to JOIN.
Example:
SELECT * FROM table1 INNER JOIN table2
ON table1.num = table2.num AND table2.value = 'value1';The result:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1
USING
You can utilize the USING clause, when columns in both tables have the same names.
USING takes a comma-separated list of the column names and creates a JOIN condition that includes an equality comparison for each column. For example, joining table1 and table2 with USING (field1, field2) produces the JOIN condition ON table1.field1 = table2.field1 AND table1.field2 = table2.field2.
The result of the JOIN clause with USING contains a single column for each of the listed column pairs in the listed order, followed by all remaining columns from table1 and table2.
Example:
SELECT * FROM table1 INNER JOIN table2 USING (num);The result:
num | name | value -----+------+-------- 1 | a | value1 3 | c | value2
NATURAL
The NATURAL clause creates a USING list. It includes all matching column names that both input tables contain. As with USING, the result of the JOIN clause with NATURAL contains a single column for each of the listed column pairs in the listed order, followed by all remaining columns from table1 and table2.
Example:
SELECT * FROM table1 NATURAL INNER JOIN table2;The result:
num | name | value -----+------+-------- 1 | a | value1 3 | c | value2
If tables do not have columns with matching names, the NATURAL JOIN clause produces the same result as CROSS JOIN.
Combine JOIN clauses
You can chain JOIN clauses of all types or nest JOIN to another JOIN. In the default order, JOIN clauses are executed from left to right. You can enclose JOIN clauses in parentheses to specify the join order.
Create table3 in addition to table1 and table2 for test examples:
CREATE TABLE table3(
num INT PRIMARY KEY,
name VARCHAR (25)
);
INSERT INTO table3 (id, value) VALUES
(1, 'value1'),
(2, 'value4');table1 table2 table3 num | name num | value id | value -----+------ -----+------ -----+------ 1 | a 1 | value1 1 | value1 2 | b 3 | value2 2 | value4 3 | c 5 | value3
Execute a JOIN query without and with parentheses and compare results.
Run a query without parentheses:
SELECT * FROM table1 CROSS JOIN table2 INNER JOIN table3 USING(value);First, PostgreSQL executes the CROSS JOIN clause SELECT * FROM table1 CROSS JOIN table2. Its result is:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1 1 | a | 3 | value2 1 | a | 5 | value3 2 | b | 1 | value1 2 | b | 3 | value2 2 | b | 5 | value3 3 | c | 1 | value1 3 | c | 3 | value2 3 | c | 5 | value3
After that, PostgreSQL executes INNER JOIN on the CROSS JOIN resulted table and table3. Since USING contains the value field, this field is the first column in the resulted table.
The result:
value | num | name | num | id --------+-----+------+-----+---- value1 | 1 | a | 1 | 1 value1 | 2 | b | 1 | 1 value1 | 3 | c | 1 | 1
Add parentheses to the query:
SELECT * FROM table1 CROSS JOIN (table2 INNER JOIN table3 USING(value));First, PostgreSQL executes the INNER JOIN clause SELECT * FROM table2 INNER JOIN table3 USING(value). Its result is:
value | num | id --------+-----+---- value1 | 1 | 1
After that, PostgreSQL executes CROSS JOIN on the table1 and the INNER JOIN resulted table.
The result:
num | name | value | num | id -----+------+--------+-----+---- 1 | a | value1 | 1 | 1 2 | b | value1 | 1 | 1 3 | c | value1 | 1 | 1