Inner joins

An inner join is a method of combining two tables that discards rows of either table that do not match any row of the other table. The matching is based on the join condition.

To request an inner join, execute a SELECT statement in which you specify the tables that you want to join in the FROM clause, and specify a WHERE clause or an ON clause to indicate the join condition. The join condition can be any simple or compound search condition that does not contain a subquery reference.

In the simplest type of inner join, the join condition is column1=column2.

Inner join example

For this example, assume that the PARTS and PRODUCTS tables contain the following rows:

          PARTS table                             PRODUCTS table
PART      PROD#   SUPPLIER              PROD#     PRODUCT        PRICE
=======   =====   ============          =====     ===========    =====
WIRE      10      ACWF                  505       SCREWDRIVER    3.70
OIL       160     WESTERN_CHEM          30        RELAY          7.55
MAGNETS   10      BATEMAN               205       SAW            18.90
PLASTIC   30      PLASTIK_CORP          10        GENERATOR      45.75
BLADES    205     ACE_STEEL

To join the PARTS and PRODUCTS tables on the PROD# column to get a table of parts with their suppliers and the products that use the parts, you can use either one of the following SELECT statements:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS, PRODUCTS
  WHERE PARTS.PROD# = PRODUCTS.PROD#;

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

The result table looks like the following output:

PART      SUPPLIER        PROD#   PRODUCT
=======   ============    =====   =========
WIRE      ACWF            10      GENERATOR
MAGNETS   BATEMAN         10      GENERATOR
PLASTIC   PLASTIK_CORP    30      RELAY
BLADES    ACE_STEEL       205     SAW

Three things about this example:

A part in the parts table (OIL) has product (#160), which is not in the products table. A product (SCREWDRIVER, #505) has no parts listed in the parts table. Neither OIL nor SCREWDRIVER appears in the result of the join.
In contrast, an outer join includes rows in which the values in the joined columns do not match.
You can explicitly specify that this join is an inner join (not an outer join). Use INNER JOIN in the FROM clause instead of the comma, and use ON to specify the join condition (rather than WHERE) when you explicitly join tables in the FROM clause.
If you do not specify a WHERE clause in the first form of the query, the result table contains all possible combinations of rows for the tables that are identified in the FROM clause. You can obtain the same result by specifying a join condition that is always true in the second form of the query, as in the following statement:
```
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON 1=1;
```
Regardless of whether you omit the WHERE clause or specify a join condition that is always true, the number of rows in the result table is the product of the number of rows in each table.

You can specify more complicated join conditions to obtain different sets of results. For example, to eliminate the suppliers that begin with the letter A from the table of parts, suppliers, product numbers, and products, write a query like the following query:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    AND SUPPLIER NOT LIKE 'A%';

The result of the query is all rows that do not have a supplier that begins with A. The result table looks like the following output:

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY

Example of joining a table to itself by using an inner join

Joining a table to itself is useful to show relationships between rows. The following example returns a list of major projects from the PROJ table and the projects that are part of those major projects.

In this example, A indicates the first instance of table DSN8C10.PROJ, and B indicates the second instance of this table. The join condition is such that the value in column PROJNO in table DSN8C10.PROJ A must be equal to a value in column MAJPROJ in table DSN8C10.PROJ B.

The following SQL statement joins table DSN8C10.PROJ to itself and returns the number and name of each major project followed by the number and name of the project that is part of it:

SELECT A.PROJNO, A.PROJNAME, B.PROJNO, B.PROJNAME
  FROM DSN8C10.PROJ A, DSN8C10.PROJ B
  WHERE A.PROJNO = B.MAJPROJ;

The result table looks similar to the following output:

PROJNO  PROJNAME                  PROJNO   PROJNAME
======  ========================  =======  ========================
AD3100  ADMIN SERVICES            AD3110   GENERAL AD SYSTEMS
AD3110  GENERAL AD SYSTEMS        AD3111   PAYROLL PROGRAMMING
AD3110  GENERAL AD SYSTEMS        AD3112   PERSONNEL PROGRAMMG
⋮
OP2010  SYSTEMS SUPPORT           OP2013   DB/DC SUPPORT

In this example, the comma in the FROM clause implicitly specifies an inner join, and it acts the same as if the INNER JOIN keywords had been used. When you use the comma for an inner join, you must specify the join condition on the WHERE clause. When you use the INNER JOIN keywords, you must specify the join condition on the ON clause.