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)
db2 join contacts table
db2 join customers table

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:

db2 join inner join example

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 join inner join

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:

db2 join left join example

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)
db2 join left join with a where clause

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)
db2 join right join example

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:

db2 join 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)
db2 join right join with a where clause

And the following Venn diagram illustrates the operation:

db2 join right join with a where clause

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:

db2 join full 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)
db2 join full join with a where clause example

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.

Was this tutorial helpful ?