内部联接

INNER JOIN 是一个 JOIN 操作，允许你指定显式连接子句。

句法

TableExpression [INNER] JOIN TableExpression {ON booleanExpression | 使用条款}

你可以通过使用布尔表达式指定 ON 来指定 join 子句。

ON 子句中的表达式范围包括当前表和当前 SELECT 的外部查询块中的任何表。在以下示例中，ON 子句引用当前表：

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