sqlite 基本和高级用法

转自：http://www.yiibai.com/sqlite/sqlite_using_joins.html

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