Inner join

You can use an inner join in a SELECT statement to retrieve only the rows that satisfy the join conditions on every specified table.

You can request an inner join, by running a SELECT statement in which you specify the tables that you want to join 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.

Begin general-use programming interface information.
Example: You can join the PARTS and PRODUCTS tables on the PROD# column to form a table of parts with their suppliers and the products that use the parts. Consider the two 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#;

Either of these statements gives this result: 

PART        SUPPLIER         PROD#     PRODUCT
=======     ============     =====     =========
WIRE        ACWF             10        GENERATOR
MAGNETS     BATEMAN          10        GENERATOR
BLADES      ACE_STEEL        205       SAW
PLASTIC     PLASTIK_CORP     30        RELAY
End general-use programming interface information.

Notice three things about this example:

  • One part in the PARTS table (OIL) has a product number (160) that is not in the PRODUCTS table. One product (505, SCREWDRIVER) has no parts listed in the PARTS table. OIL and SCREWDRIVER do not appear in the result of the join.
  • Explicit syntax expresses that this join is an inner join. You can use INNER JOIN in the FROM clause instead of the comma. ON (rather than WHERE) specifies the join condition 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.
    Begin general-use programming interface information.
    Example: Consider this query: 
    SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
  ON 1=1;

    The number of rows in the result table is the product of the number of rows in each table: 

    PART        SUPPLIER         PROD#     PRODUCT
=======     ============     =====     ===========
WIRE        ACWF             10        SCREWDRIVER
WIRE        ACWF             10        RELAY
WIRE        ACWF             10        SAW
WIRE        ACWF             10        GENERATOR
OIL         WESTERN_CHEM     160       SCREWDRIVER
OIL         WESTERN_CHEM     160       RELAY
OIL         WESTERN_CHEM     160       SAW
OIL         WESTERN_CHEM     160       GENERATOR
⋮
    End general-use programming interface information.

You can specify more complicated join conditions to obtain different sets of results.

Begin general-use programming interface information.
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 example: 
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: 

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     =========
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
Example: Start of changeThis example joins the PROJ table to itself by using an inner join. The query 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 AS "MAJOR PROJ",       
       A.PROJNAME AS "MAJOR PROJ NAME",
       B.PROJNO AS "PROJ #",           
       B.PROJNAME AS "PROJ NAME"       
  FROM PROJ A, PROJ B                  
  WHERE A.PROJNO=B.MAJPROJ;

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

The result table looks like the following example: 

MAJOR PROJ  MAJOR PROJ NAME        PROJ #     PROJ NAME
==========  ===============        ======     ====================
AD3100      ADMIN SERVICES         AD3110     GENERAL AD SYSTEMS  
AD3110      GENERAL AD SYSTEMS     AD3111     PAYROLL PROGRAMMING 
AD3110      GENERAL AD SYSTEMS     AD3112     PERSONNEL PROGRAMMG 
AD3110      GENERAL AD SYSTEMS     AD3113     ACCOUNT.PROGRAMMING 
MA2100      WELD LINE AUTOMATION   MA2110     W L PROGRAMMING     
MA2110      W L PROGRAMMING        MA2111     W L PROGRAM DESIGN  
MA2110      W L PROGRAMMING        MA2112     W L ROBOT DESIGN    
MA2110      W L PROGRAMMING        MA2113     W L PROD CONT PROGS 
OP1000      OPERATION SUPPORT      OP1010     OPERATION           
OP2000      GEN SYSTEMS SERVICES   OP2010     SYSTEMS SUPPORT     
OP2010      SYSTEMS SUPPORT        OP2011     SCP SYSTEMS SUPPORT 
OP2010      SYSTEMS SUPPORT        OP2012     APPLICATIONS SUPPORT
OP2010      SYSTEMS SUPPORT        OP2013     DB/DC SUPPORT       
MA2100      WELD LINE AUTOMATION   PL2100     WELD LINE PLANNING

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 in the WHERE clause. When you use the INNER JOIN keywords, you must specify the join condition in the ON clause.

End of change
End general-use programming interface information.