Using joins to combine data from different tables in PostgreSQL

Inner join

The default join is called an inner join. In PostgreSQL, this can be specified using either INNER JOIN or just simply JOIN.

Here is a typical example demonstrating the syntax of an inner join:

SELECT
    *
FROM
    table_1
[INNER] JOIN table_2
    ON table_1.id = table_2.table_1_id;

An inner join is the most restrictive type of join because it only displays rows created by combining rows from each table. Any rows in the constituent tables that did not have a matching counterpart in the other table are removed from the results. For example, if the first table has a value of "blue" in the comparison column, and the second table has no record with that value, that row will be suppressed from the output.

If you represent the results as a Venn diagram of the component tables, an inner join allows you to represent the overlapping area of the two circles. None of values that only existed in one of the tables are displayed.

Left join

A left join is a join that shows all of the records found in an inner join, plus all of the unmatched rows from the first table. In PostgreSQL, this can be specified as a LEFT OUTER JOIN or as just a LEFT JOIN.

The basic syntax of a left join follows this pattern:

SELECT
    *
FROM
    table_1
LEFT JOIN table_2
    ON table_1.id = table_2.table_1_id;

A left join is constructed by first performing an inner join to construct rows from all of the matching records in both tables. Afterwards, the unmatched records from the first table are also included. Since each row in a join includes the columns of both tables, the unmatched columns use NULL as the value for all of the columns in the second table.

If you represent the results as a Venn diagram of the component tables, a left join allows you to represent the entire left circle. The parts of the left circle represented by the intersection between the two circles will have additional data supplemented by the right table.

Right join

A right join is a join that shows all of the records found in an inner join, plus all of the unmatched rows from the second table. In PostgreSQL, this can be specified as a RIGHT OUTER JOIN or as just a RIGHT JOIN.

The basic syntax of a right join follows this pattern:

SELECT
    *
FROM
    table_1
RIGHT JOIN table_2
    ON table_1.id = table_2.table_1_id;

A right join is constructed by first performing an inner join to construct rows from all of the matching records in both tables. Afterwards, the unmatched records from the second table are also included. Since each row in a join includes the columns of both tables, the unmatched columns use NULL as the value for all of the columns in the first table.

If you represent the results as a Venn diagram of the component tables, a right join allows you to represent the entire right circle. The parts of the right circle represented by the intersection between the two circles will have additional data supplemented by the left table.

Full join

A full join is a join that shows all of the records found in an inner join, plus all of the unmatched rows from both component tables. In PostgreSQL, this can be specified as a FULL OUTER JOIN or as just a FULL JOIN.

The basic syntax of a full join follows this pattern:

SELECT
    *
FROM
    table_1
FULL JOIN table_2
    ON table_1.id = table_2.table_1_id;

A full join is constructed by first performing an inner join to construct rows from all of the matching records in both tables. Afterwards, the unmatched records from both tables are also included. Since each row in a join includes the columns of both tables, the unmatched columns use NULL as the value for all of the columns in the unmatched other table.

If you represent the results as a Venn diagram of the component tables, a full join allows you to represent both of the component circles entirely. The intersection of the two circles will have values supplied by each of the component tables. The parts of the circles outside of the overlapping area will have the values from the table they belong to, using NULL to fill in the columns found in the other table.

Cross join

A special join called a CROSS JOIN is also available. A cross join does not use any comparisons to determine whether the rows in each table match one another. Instead, results are constructed by simply adding each of the rows from the first table to each of the rows of the second table.

This produces a Cartesian product of the rows in two or more tables. In effect, this style of join combines rows from each table unconditionally. So, if each table has three rows, the resulting table would have nine rows containing all of the columns from both tables.

For example, if you have a table called t1 combined with a table called t2, each with rows r1, r2, and r3, the result would be nine rows combined like so:

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

Self join

A self join is any join that combines the rows of a table with itself. It may not be immediately apparent how this could be useful, but it actually has many common applications.

Often, tables describe entities that can fulfill multiple roles in relationship to one another. For instance, if you have a table of people, each row could potentially contain a mother column that reference other people in the table. A self join would allow you to stitch these different rows together by joining a second instance of the table to the first where these values match.

Since self joins reference the same table twice, table aliases are required to disambiguate the references. In the example above, for instance, you could join the two instances of the people table using the aliases people AS children and people AS mothers. That way, you can specify which instance of the table you are referring to when defining join conditions.

Here is another example, this time representing relationships between employees and managers:

SELECT
    *
FROM
    people AS employee
JOIN people AS manager
    ON employee.manager_id = manager.id;

The ON clause

The most standard way of defining the conditions for table joins is with the ON clause. The ON clause uses an equals sign to specify the exact columns from each table that will be compared to determine when a join may occur. PostgreSQL uses the provided columns to stitch together the rows from each table.

The ON clause is the most verbose, but also the most flexible of the available join conditions. It allows for specificity regardless of how standardized the column names are of each table being combined.

The basic syntax of the ON clause looks like this:

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.ident;

Here, the rows from table1 and table2 will be joined whenever the id column from table1 matches the ident column from table2. Because an inner join is used, the results will only show the rows that were joined. Since the query uses the wildcard * character, all of the columns from both tables will be displayed.

This means that both the id column from table1 and the ident column from table2 will be displayed, even though they have the same exact value by virtue of satisfying the join condition. You can avoid this duplication by calling out the exact columns you wish to display in the SELECT column list.

The USING clause

The USING clause is a shorthand for specifying the conditions of an ON clause that can be used when the columns being compared have the same name in both tables. The USING clause takes a list, enclosed in parentheses, of the shared column names that should be compared.

The general syntax of the USING clause uses this format:

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state);

This join combines table1 with table2 when two columns that both tables share (id and state) each have matching values.

This same join could be expressed more verbosely using ON like this:

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state;

While both of the above joins would result in the same rows being constructed with the same data present, they would be displayed slightly different. While the ON clause includes all of the columns from both tables, the USING clause suppresses the duplicate columns. So instead of there being two separate id columns and two separate state columns (one for each table), the results would just have one of each of the shared columns, followed by all of the other columns provided by table1 and table2.

The NATURAL clause

The NATURAL clause is yet another shorthand that can further reduce the verbosity of the USING clause. A NATURAL join does not specify any columns to be matched. Instead, PostgreSQL will automatically join the tables based on all columns that have matching columns in each database.

The general syntax of the NATURAL join clause looks like this:

SELECT
    *
FROM
    table1
NATURAL JOIN
    table2;

Assuming that table1 and table2 both have columns named id, state, and company, the above query would be equivalent to this query using the ON clause:

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company;

And this query using the USING clause:

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state, company);

Like the USING clause, the NATURAL clause suppresses duplicate columns, so there would be only a single instance of each of the joined columns in the results.

While the NATURAL clause can reduce the verbosity of your queries, care must be exercised when using it. Because the columns used for joining the tables are automatically calculated, if the columns in the component tables change, the results can be vastly different due to new join conditions.