Db2 RIGHT JOIN

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

Introduction to Db2 RIGHT JOIN clause

The RIGHT JOIN clause is a reversed version of the LEFT JOIN clause. The RIGHT JOIN clause allows you to query data from two or more tables.

Suppose, you have two tables named T1 and T2, which are called the left table and the right table respectively.

The RIGHT JOIN clause selects data starting from the right table (T2). It compares each row in the right table (T2) with every row in the left table (T1). If two rows satisfy the join condition, the RIGHT JOIN clause combines columns of these rows into a new row and includes this new row in the result.

In case a row in the right table does not have a matching row in the left table, the RIGHT JOIN clause still combines the columns of the row in the right table with the columns of the row in the left table. However, the columns in the left table will have NULL values.

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

This Venn diagram illustrates how to use the RIGHT JOIN clause to join T1 and T2 tables:

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

SELECT 
    select_list
FROM
    T1
RIGHT 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 T2 with the values of the columns in the table T1 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 like T1.id and T2.id. If the table names are long, you can use the table aliases to save some typing.

The following Venn diagram illustrates the RIGHT JOIN of two tables T1 and T2:

The following statement illustrates how to join more than two tables using the RIGHT JOIN clauses:

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

Db2 RIGHT JOIN examples

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

1) Using DB2 RIGHT JOIN to join two tables example

The following database diagram displays the books and publishers tables:

In this data model, a publisher may have zero or many books while a book may belong 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 was not known at the time of recording, the value in the publisher_id column of the books table is NULL.

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

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

This picture shows the partial result set:

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

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

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

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

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

Here is the result set:

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