INNER JOIN

An INNER JOIN is a JOIN operation that allows you to specify an explicit join clause.

Syntax

TableExpression [ INNER ] JOIN TableExpression
{
    ON booleanExpression }
    }

You can specify the join clause by specifying ON with a boolean expression.

The scope of expressions in the ON clause includes the current tables and any tables in outer query blocks to the current SELECT. In the following example, the ON clause refers to the current tables:

SELECT *
FROM SAMP.EMPLOYEE INNER JOIN SAMP.STAFF
ON EMPLOYEE.SALARY < STAFF.SALARY;

The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).

Examples

-- Join the EMP_ACT and EMPLOYEE tables 
-- select all the columns from the EMP_ACT table and 
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
     FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
     ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO;
-- Join the EMPLOYEE and DEPARTMENT tables, 
-- select the employee number (EMPNO), 
-- employee surname (LASTNAME), 
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table) 
-- and department name (DEPTNAME) 
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME 
     FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT 
     ON WORKDEPT = DEPTNO 
     AND YEAR(BIRTHDATE) < 1930;
 
 -- Another example of "generating" new data values,  
-- using a query which selects from a VALUES clause (which is an 
-- alternate form of a fullselect). 
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1;
 
This results in:
   C1         |C2         |C1         |2
-----------------------------------------------
3          |4          |3          |2
1          |5          |1          |2
 
-- List every department with the employee number and 
-- last name of the manager 
 
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
    FROM DEPARTMENT INNER JOIN EMPLOYEE
    ON MGRNO = EMPNO;
 
 -- List every employee number and last name 
-- with the employee number and last name of their manager 
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME 
    FROM EMPLOYEE E INNER JOIN  
    DEPARTMENT INNER JOIN EMPLOYEE M 
        ON MGRNO = M.EMPNO
        ON E.WORKDEPT = DEPTNO;