Db2 Join

Summary: in this tutorial, you will learn how to query data from two tables using Db2 joins.

When you want to view the data from multiple tables, you can use the SELECT statement with joins. The join associates the rows from one table with rows from another table based on a specified condition, typically of matching column values.

Db2 supports various kinds of joins including inner join, left outer join, right outer join, and full outer join.

Let’s setup some sample tables for demonstration.

Setting sample tables

Second, create two new tables named contacts and customers:

CREATE TABLE contacts (
    contact_id INT NOT NULL PRIMARY KEY, 
    name       VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
    customer_id INT NOT NULL PRIMARY KEY, 
    name        VARCHAR(100) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the contacts and customers tables:

INSERT INTO contacts(contact_id, name)
VALUES(1,'Amelia'),
    (2,'Olivia'),
    (3,'Isla'),
    (4,'Emily');


INSERT INTO customers(customer_id, name)
VALUES(1,'Amelia'),
    (2,'Isla'),
    (3,'Jessica'),
    (4,'Lily');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the contacts and customers tables:

SELECT 
    *
FROM 
    contacts;

SELECT 
    *
FROM 
    customers;
Code language: SQL (Structured Query Language) (sql)

Let’s call the contacts table the left table and the customers table the right table.

Db2 Inner Join

The inner join combines each row from the left table with rows of the right table, it keeps only the rows in which the join condition is true.

This example uses the INNER JOIN to get the rows from the contacts table that have the corresponding rows with the same values in the name column of the customers table:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    INNER JOIN customers cu 
        ON cu.name = co.name;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

In this example, the join condition is cu.name = co.name which matches the values in the name column of the contacts table with the values in the name column of the customers table.

The following Venn diagram illustrates the result of the inner join of two result sets:

Db2 Left Join

The left join selects data starting from the left table and matches rows in the right table. Similar to the inner join, the left join returns all rows from the left table and the matching rows from the right table. In addition, if a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls.

Note that the left join is also called the left outer join. The outer keyword is optional.

The following statement joins the contacts table with the customers table using left join:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    LEFT JOIN customers cu 
        ON cu.name = co.name;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

This Venn diagram illustrates the left join of two result sets:

To get the rows that available only in the left table but not in the right table, you add a WHERE clause to the above query:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    LEFT JOIN customers cu 
        ON cu.name = co.name
WHERE
    cu.name IS NULL;
Code language: SQL (Structured Query Language) (sql)

And the this Venn diagram illustrates the left join that selects rows available only in the left table:

Db2 Right Join

The right join or right outer join, which is a reversed version of the left join, selects data starting from the right table and matches with the rows in the left table.

The right join returns a result set that includes all the rows from the right table and the matching rows in the left table. If a row in the right table does not have a matching row in the left table, all columns in the left table will contain nulls.

The following example uses the right join to query rows from contacts and customers tables:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    RIGHT JOIN customers cu 
        ON cu.name = co.name;
Code language: SQL (Structured Query Language) (sql)

Notice that all the rows from the right table (customers) are included in the result set.

Here is the Venn diagram of the right join:

In order to get rows that are available only in the right table, you add a WHERE clause to the above query:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    RIGHT JOIN customers cu 
        ON cu.name = co.name
WHERE
    co.name IS NULL;
Code language: SQL (Structured Query Language) (sql)

And the following Venn diagram illustrates the operation:

Db2 full join

The full join returns a result set that includes all the rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have nulls.

Note that full join and full outer join are synonyms. The outer keyword is optional.

This example performs a full join between the contacts and customers tables:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    FULL JOIN customers cu 
        ON cu.name = co.name;
Code language: SQL (Structured Query Language) (sql)

The Venn diagram that illustrates the full outer join:

To select rows that are available in either left or right table, you exclude rows that are common to both tables by adding a WHERE clause to the above query:

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    FULL OUTER JOIN customers cu 
        ON cu.name = co.name
WHERE
    co.name IS NULL OR
    cu.name IS NULL;
Code language: SQL (Structured Query Language) (sql)

The following Venn diagram illustrates the above operation:

In this tutorial, you have learned Db2 joins including inner join, left outer join, right outer join, and full outer join to combine rows from two tables.