DB2 - SQL INNER JOIN

Inner Joins or Equal Joins are joins which include only the rows where the values in the joined columns match.

You can code inner joins either by Implicit Syntax or Explicit Syntax.

Syntax: Implicit

SELECT column_name(s)
  FROM table1
  INNER JOIN table2
    ON table1.column_name = table2.column_name;

Syntax: Explicit

SELECT column_name(s)
  FROM table1, table2
 WHERE table1.column_name = table2.column_name;

DB2 Database:

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.

The following SQL statement selects all orders with customer information.

Example:

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

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown.

Result:

The following SQL statement is same as above example SQL statement.

SELECT Orders.Orderid, Customers.Customername, Orders.Orderdate
  FROM Orders, Customers
  Where Orders.Customernumber=Customers.Customernumber;

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