Sqlite 简明教程
SQLite - JOINS
SQLite Joins 子句用于将数据库中两个或更多表中的记录组合在一起。JOIN 是通过使用各个字段共有的值将来自两个表的字段组合在一起的一种方法。
SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
SQL 定义了三类主要联接 −
SQL defines three major types of joins −
-
The CROSS JOIN
-
The INNER JOIN
-
The OUTER JOIN
在我们继续之前，让我们考虑两个表 COMPANY 和 DEPARTMENT。我们已经看到了用于填充 COMPANY 表的 INSERT 语句。因此，让我们假设 COMPANY 表中可用的记录列表 −
Before we proceed, let’s consider two tables COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let’s assume the list of records available in COMPANY table −
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，具有以下定义 −
Another table is DEPARTMENT with the following definition −
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);以下是用于填充 DEPARTMENT 表的 INSERT 语句列表 −
Here is the list of INSERT statements to populate DEPARTMENT table −
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 表中拥有以下记录列表 −
Finally, we have the following list of records available in DEPARTMENT table −
ID DEPT EMP_ID
---------- ---------- ----------
1 IT Billing 1
2 Engineering 2
3 Finance 7The CROSS JOIN
CROSS JOIN 将第一张表中的每一行与第二张表中的每一行匹配。如果输入表分别有 x 和 y 行，则结果表将有 x*y 行。由于 CROSS JOIN 有可能生成极大的表格，因此必须小心地在适当的时候才使用它们。
CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y row, respectively, the resulting table will have x*y row. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate.
下面是 CROSS JOIN 的语法：
Following is the syntax of CROSS JOIN −
SELECT ... FROM table1 CROSS JOIN table2 ...基于上述表格，您可以按如下方式编写一个 CROSS JOIN：
Based on the above tables, you can write a CROSS JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;上面的查询将产生以下结果：
The above query will produce the following result −
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 FinanceThe INNER JOIN
INNER JOIN 通过基于连接谓词将两个表（table1 和 table2）的列值组合在一起，来创建一个新的结果表。该查询将 table1 的每一行与 table2 的每一行比较，找到满足连接谓词的所有行对。当连接谓词得到满足时，A 和 B 每对匹配行的列值将被组合到一个结果行中。
INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a result row.
INNER JOIN 是最常见且默认的连接类型。您可以选择使用 INNER 关键字。
An INNER JOIN is the most common and default type of join. You can use INNER keyword optionally.
下面是 INNER JOIN 的语法：
Following is the syntax of INNER JOIN −
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...为了避免冗余并使短语更短，可以使用 USING 表达式声明 INNER JOIN 条件。此表达式指定一个或多个列的列表。
To avoid redundancy and keep the phrasing shorter, INNER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns.
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...NATURAL JOIN 类似于 JOIN…USING ，只是它会自动测试两个表中存在的所有列的值是否相等：
A NATURAL JOIN is similar to a JOIN…USING, only it automatically tests for equality between the values of every column that exists in both tables −
SELECT ... FROM table1 NATURAL JOIN table2...基于上述表格，您可以按如下方式编写一个 INNER JOIN：
Based on the above tables, you can write an INNER JOIN as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;上面的查询将产生以下结果：
The above query will produce the following result −
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
7 James FinanceThe OUTER JOIN
OUTER JOIN 是 INNER JOIN 的扩展。尽管 SQL 标准定义了三种类型的 OUTER JOIN：LEFT、RIGHT 和 FULL，但 SQLite 仅支持 LEFT OUTER JOIN 。
OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the LEFT OUTER JOIN.
OUTER JOIN 有一个与 INNER JOIN 相同的条件，它使用 ON、USING 或 NATURAL 关键字来表示。初始结果表以相同的方式计算。一旦计算出主连接，OUTER JOIN 将从一个或两个表中获取任何未连接的行，用 NULL 填充它们，并把它们附加到结果表中。
OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER JOIN will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table.
下面是 LEFT OUTER JOIN 的语法：
Following is the syntax of LEFT OUTER JOIN −
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...为了避免冗余并使短语更短，可以使用 USING 表达式声明 OUTER JOIN 条件。此表达式指定一个或多个列的列表。
To avoid redundancy and keep the phrasing shorter, OUTER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns.
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...基于上述表格，您可以按如下方式编写一个外部连接：
Based on the above tables, you can write an outer join as follows −
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;上面的查询将产生以下结果：
The above query will produce the following result −
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
Teddy
Mark
David
Kim
7 James Finance