DB2 - SQL JOIN Clause

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

A join is a mechanism used to associate tables within a SELECT statement.

For creating a join, you must specify all the tables to be included and how they are related to each other.

Let's look at the "Orders" table data below:

Let's look at the "Customers" table data below:

Notice that the "Customernumber" column in the "Orders" table refers to the "Customernumber" in the "Customers" table. The relationship between the two tables above is the "Customernumber" column.

Let us see how to create SQL statement that selects records that have matching values in both tables.

Example:

SELECT Orders.Orderid, Customers.Customername, Orders.Orderdate
  FROM Orders
  INNER JOIN Customers ON Orders.Customernumber=Customers.Customernumber;

Result:

Explanation:

The SELECT statement starts in the same way as all the statements you have looked at so far, by specifying the columns to be retrieved. The big difference here is that two of the specified columns (Orderid and Orderdate) are in one table, whereas the other (Customername) is in another table.

Unlike all the prior SELECT statements, this one has two tables listed in the FROM clause, Orders and Customers.

You must use the fully qualified column name (table and column separated by a period) whenever there is a possible ambiguity about which column you are referring to. In this case, you specify Orders.Orderid and Customers.Customername.

Types of Joins:

There are two types of joins:

• Inner Join - Retrieve matching values in both tables.

• Outer Join - Retrieve all records from the left table, and the matched records from the right table.

  1. Left Outer Join - Retrieve all records from the left table, and the matched records from the right table.

  2. Right Outer Join - Retrieve all records from the right table, and the matched records from the left table.

  3. Full Outer Join - Retrieve all records from both table.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!