TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

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:

OrderidCustomernumberOrderdate
1001102020-09-08
1002992020-09-01
1003172020-08-25
1004762020-09-19
1005442020-09-25

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

CustomernumberCustomernameCountry
76JackAmerica
17JancyGermany
20CarmenRussia
10RobertIndia
99BrianChina

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:
OrderidCustomernameOrderdate
1001Robert2020-09-08
1002Brian2020-09-01
1003Jancy2020-08-25
1004Jack2020-09-19

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!

Are you looking for Job Change? Job Portal