SQLite联接

SQLite Joins 子句用于合并数据库中两个或多个表中的记录。 JOIN是一种通过使用每个表的公用值来组合两个表中的字段的方法。

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

• 交叉联接

• 内部联接

• 外联接

在继续之前，让我们考虑两个表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           Engineering 2
3           Finance     7

交叉联接

CROSS JOIN将第一个表的每一行与第二个表的每一行匹配。如果输入表分别具有x和y行，则结果表将具有x * y行。由于CROSS JOINs可能会生成非常大的表，因此必须注意仅在适当的时候使用它们。

以下是CROSS JOIN的语法：

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

根据上表，你可以编写CROSS JOIN，如下所示：

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

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

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

内部联接

INNER JOIN通过基于连接谓词组合两个表(table1和table2)的列值来创建新的结果表。该查询将table1的每一行与table2的每一行进行比较，以找到满足join谓词的所有行对。当满足连接谓词时，A和B的每个匹配行对的列值将合并为结果行。

INNER JOIN是最常见的默认联接类型。你可以选择使用INNER关键字。

以下是INNER JOIN的语法：

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

为了避免冗余并缩短短语，可以用一个声明INNER JOIN条件。USING表达。此表达式指定一个或多个列的列表。

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

NATURAL JOIN类似于JOIN...USING，只有它会自动测试两个表中每个列的值是否相等：

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

根据上表，你可以编写一个INNER JOIN，如下所示：

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       Engineering
7           James       Finance

外连接

OUTER JOIN是INNER JOIN的扩展。尽管SQL标准定义了三种外部连接：LEFT，RIGHT和FULL，但是SQLite仅支持左外连接.

外部联接的条件与内部联接相同，使用ON，USING或NATURAL关键字表示。初始结果表的计算方法相同。一旦计算了主JOIN，OUTER JOIN将从一个或两个表中获取所有未连接的行，将它们填充为NULL，然后将它们附加到结果表中。

以下是LEFT OUTER JOIN的语法：

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

为了避免冗余并缩短短语，可以使用USING表达式声明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       Engineering
                Teddy
                Mark
                David
                Kim
7           James       Finance