1.29. SQLite Join

SQLite’s Join Clause is used to combine records of tables in two or more databases. JOIN is a means of combining fields in two tables through common values.

SQL Three main types of connections are defined:

Cross connect-CROSS JOIN
Internal connection-INNER JOIN
External connection-OUTER JOIN

Before we move on, let’s assume that there are two tables COMPANY And DEPARTMENT . We’ve seen it used to fill COMPANY Tabular INSERT Statement. Now let’s assume COMPANY The list of records for the table is as follows:

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 

The other watch is DEPARTMENT Which is defined as follows:

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

Here is the fill DEPARTMENT Tabular INSERT Statement:

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 ); 

Finally, we are in DEPARTMENT The table has the following list of records:

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

1.29.1. Cross connect-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 rows respectively, the result table has x rows. Because cross-joins (CROSS JOIN) have the potential to produce very large tables, you must be careful to use them only when appropriate.

Cross-join operations that return the Cartesian product of all data rows of the two tables being joined, and the number of data rows returned is equal to the number of data rows in the first table that meet the query criteria multiplied by the number of rows in the second table that meet the query criteria.

The following is the syntax for CROSS JOIN:

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

Based on the above table, we can write a CROSS JOIN, as follows:

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

The above query produces 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 

1.29.2. Internal connection-INNER JOIN ¶

Inner join (INNER JOIN) creates a new result table based on the join predicate combining the column values of the two tables (table1 and table2). The query compares each row in table1 with each row in table2 to find a match for all rows that satisfy the join predicate. When the join predicate is satisfied, the column values of each match of rows An and B are merged into a single result row.

Internal connection (INNER JOIN) is the most common connection type and is the default connection type. The INNER keyword is optional.

Here is the syntax for INNER JOIN:

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

To avoid redundancy and keep the wording short, you can use the USING The expression declares the INNER JOIN condition. This expression specifies a list of one or more columns:

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

Natural connection (NATURAL JOIN) is similar to JOIN…USING Except that it automatically tests the equivalence between the values of each column in the two tables

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

Based on the above table, we can write an inner join (INNER JOIN), as follows:

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

The above query produces the following results:

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

1.29.3. External connection-OUTER JOIN ¶

External connection (OUTER JOIN) is an extension of inner connection (INNER JOIN). Although the SQL standard defines three types of external connections: LEFT, RIGHT, and FULL, SQLite only supports 左外连接（LEFT OUTER JOIN） .

External connections (OUTER JOIN) declare conditions in the same way as inner connections (INNER JOIN), using ON , USING or NATURAL Keyword to express. The initial result table is calculated in the same way. Once the primary join calculation is complete, the outer join (OUTER JOIN) will merge in from any unjoined rows in one or two tables, and the externally joined columns will be used NULL Values to attach them to the result table.

Here is the syntax for the left outer join (LEFT OUTER JOIN):

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

To avoid redundancy and keep the wording short, you can use the USING The expression declares outer join (OUTER JOIN) conditions. This expression specifies a list of one or more columns:

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

Based on the above table, we can write an external join (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 produces the following results:

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