SQLite JOINS连接

SQLite的连接子句用于从两个或多个数据库中的表的记录相结合。JOIN是一种手段，从两个表中使用常见于每个值相结合的字段。

SQL定义了三种主要类型的联接：

• 交叉连接 - CROSS JOIN

• 内连接 - INNER JOIN

• 外连接 - OUTER JOIN

在开始之前，让我们考虑两个表COMPANY 和DEPARTMENT。我们已经看到了INSERT语句来填充COMPANY表。所以只是让我们假设COMPANY 表的记录清单： 

ID          NAME        AGE         ADDRESS     SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0

另一张表是部门DEPARTMENT 具有下列定义：

CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY      NOT NULL, DEPT           CHAR(50) NOT NULL, EMP_ID         INT      NOT NULL );

这里是填充DEPARTMENT表的INSERT语句列表：

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

最后，我们有下面的列表中的记录，可在DEPARTMENT表：

ID          DEPT        EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineerin 2 3 Finance 7

交叉连接 - CROSS JOIN

交叉连接匹配的第一个表与第二个表中的每一行的每一行。如果输入表，结果表x和y列，分别有X + Y列。由于交叉联接有可能产生非常大的表，必须谨慎，只在适当的时候使用它们。

以下是交叉连接的语法：

SELECT ... FROM table1 CROSS JOIN table2 ...

根据上面的表上，我们可以写一个交叉连接如下：

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

上面的查询会产生以下结果：

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance
1           David       IT Billing
2           David       Engineerin
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance
1           James       IT Billing
2           James       Engineerin
7           James       Finance

内连接 - INNER JOIN

INNER JOIN 创建一个新的结果表中，通过结合两个表（表1和表2）根据联接谓词的列值。查询比较table1中的每一行，每一行表2，找到所有对满足连接谓词的行。当联接谓词满意，每个匹配的一对排列值A和B合并成一个结果行。

内部联接是最常见的连接类型和连接是默认的类型。可以使用可选INNER关键字。

以下是内连接的语法：

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

为了避免冗余，并保持较短的措辞，可以声明一个使用表达式内连接条件。这个表达式指定一个或多个列的列表：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

自然联接是类似于一个JOIN...USING，只是它自动测试存在两个表中的每一列的值之间相等：

SELECT ... FROM table1 NATURAL JOIN table2...

根据上面的表上，我们可以写一个内连接如下：

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

上面的查询会产生以下结果：

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance

外连接 - OUTER JOIN

外连接是一个扩展的内连接。虽然SQL标准定义了三种类型的外连接：LEFT, RIGHT,FULL，但只支持SQLite 左外连接。

外连接有一个条件，是为内部连接相同，表示使用 ON, USING, 或NATURAL关键字。初步的研究结果表中的计算相同的方式。一旦主连接是计算，OUTER JOIN将采取任何未连接的行，从一个或两个表，他们使用空值，将它们附加到生成的表。 

以下是LEFT OUTER JOIN 的语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

为了避免冗余和OUTER JOIN条件保持较短的措辞，可以声明一个使用表达式。这个表达式指定一个或多个列的列表：

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

根据上面的表上，我们可以写一个内部联接如下：

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

上面的查询会产生以下结果：

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy
            Mark
            David
            Kim
7           James       Finance