SQLite Joins

SQLite's Joins clause is used to join records from two or more tables in the database. JOIN is a means of combining fields from two tables through a common value.

SQL defines three main types of joins:

Cross join-CROSS JOIN
Inner join-INNER JOIN
OUTER JOIN- OUTER JOIN

Before we continue, let us assume that there are two tables COMPANY and DEPARTMENT. We have already seen the INSERT statement used to populate the COMPANY table. Now let us assume that the record list of the COMPANY table is as follows: ---------------------------------------------------------------------------- steal 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
##

Another table is DEPARTMENT, defined as follows:

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

The following is the INSERT statement to populate the DEPARTMENT table:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES ( 1, 'It Billing', 1);

## 最后 Finally, we have the following records in the department table:

# ID ------- ---------- ----------

1 IT Billing 1
2 Engineerin 2
3 Finance 7

Cross join - CROSS JOIN

Cross join (CROSS JOIN) matches each row of the first table with each row of the second table. If the two input tables have x and y columns, the result table has x+y columns. Since cross joins (CROSS JOIN) may produce very large tables, they must be used with caution and only use them when appropriate.

The following is the syntax of cross join (CROSS JOIN):

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

Based on the above table, we can write a cross join (CROSS JOIN) as follows:

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

The above query will produce the following results:

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）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。

下面是内连接（INNER JOIN）的语法：

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

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

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;

The above query will produce the following results:

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

外连接 - OUTER JOIN

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（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 ,... ) ...

基于上面的表，我们可以写一个外连接（OUTER JOIN），如下所示：

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

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

← SQLite constraints

SQLite Unions child clause →

SQLite Joins

内连接 - INNER JOIN

外连接 - OUTER JOIN

php.cn