# INNER JOIN operation

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

#### Syntax

    TableExpression [ INNER ] JOIN TableExpression
    {
        ON booleanExpression |
        USING clause
    }

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).

    -- 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

Parent topic: JOIN operations