Db2 LEFT JOIN

Summary: in this tutorial, you will learn how to use the Db2 LEFT JOIN clause to query data from multiple tables.

Introduction to Db2 LEFT JOIN clause

The LEFT JOIN clause is one of the joins that allows you to query data from two or more tables. Suppose, you have two tables: T1 and T2, called the left and the right tables respectively.

The LEFT JOIN clause selects data starting from the left table (T1). It compares each row in the left table with every row in the right table. If two rows match, the LEFT JOIN combines columns of these two rows into a row and includes this row in the final result set.

In case a row from the left table does not have any matching row from the right table, the LEFT JOIN also combines the columns of the row from the left table with the columns of the right table. However, the columns from the right table will have NULL values.

In other words, the LEFT JOIN clause returns all rows from the left table ( T1) and matching rows or NULL values from the right table ( T2).

This Venn diagram illustrates the LEFT JOIN of two tables T1 and T2:

The following shows the syntax of joining two tables using the LEFT JOIN clause:

SELECT 
    select_list
FROM
    T1
LEFT JOIN T2 
    ON join_condition;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the join_condition is a Boolean expression that evaluates to true, false, and unknown. Typically, it matches the values of the columns in the table T1 with the values of the columns in the table T2 using the equality operator (=).

Note that in case T1 and T2 tables have the same column names, you have to fully qualify these column names in the query e.g., T1.id and T2.id. If the table names are long, you can use the table aliases to save some typing.

To join more than two tables using the LEFT JOIN clause, you add more LEFT JOIN clauses as shown in the following query:

SELECT 
    select_list
FROM
    T1
LEFT JOIN T2 ON join_condition2
LEFT JOIN T3 on join_condition3
...;
Code language: SQL (Structured Query Language) (sql)

Db2 LEFT JOIN examples

Let’s take some examples of using the LEFT JOIN clause.

1) Using DB2 LEFT JOIN to join two tables example

The following diagram shows the books and publishers tables:

In this data model, a publisher may have zero or many books while each book belongs to zero or one publisher. The relationship between the books table and the publishers table is zero-to-many.

The publisher_id column of the books table links to the publisher_id column of the publishers table to establish this relationship.

If a book does not associate with a publisher, maybe the publisher is unknown at the time of recording, the value in the publisher_id column of the books table is NULL.

This query uses the LEFT JOIN clause to join the books table with the publishers table:

SELECT 
    b.title, 
    p.name
FROM 
    books b
    LEFT JOIN publishers p 
        ON p.publisher_id = b.publisher_id
ORDER BY 
    b.title;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this example, the LEFT JOIN clause compares the value in the publisher_id column of each row in the books table with the value in the publisher_id column of each row in the publishers table. If they are equal, the LEFT JOIN combines columns of these two rows into a row and includes this row in the result set.

If a row from the books table that does not have a matching row in the publishers table, the publisher column in the result set will contain a NULL.

2) Using DB2 LEFT JOIN to find the missing rows in another table

The following query uses the LEFT JOIN clause to find books that do not have publishers:

SELECT 
    b.title, 
    p.name publisher
FROM 
    books b
    LEFT JOIN publishers p 
        ON p.publisher_id = b.publisher_id
WHERE 
    p.name IS NULL
ORDER BY 
    b.title;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

In this tutorial, you have learned how to use the Db2 LEFT JOIN clause to query data from two tables.