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

Syntax tableExpression [ INNER ] JOIN tableExpression { ON booleanExpression | USING clause }

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

ON clausepart of INNER JOIN expressionThe 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