SQLite Joins

SQLite Joinsclauses are used to merge records from two or more tables in a database. JOIN is a method to combine fields from two tables by using common values from each table.

SQL defines three main types of joins-

• Cross join

• internal join

• outer join

Before we continue, let's consider two tables COMPANY and DEPARTMENT. We have already seen INSERT statements to populate the COMPANY table. So let's assume the list of available records in the 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

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
;

This is a list of INSERT statements used to populate the 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　;

Finally, we have the following list of available records in the DEPARTMENT table-

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

CROSS JOIN - Cross join

CROSS JOIN matches each row of the first table with each row of the second table. If the input tables have x and y rows respectively, the resulting table will have x * y rows. Since CROSS JOINs may generate very large tables, they must be used with caution only when appropriate.

The following is the syntax of CROSS JOIN-

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

According to the table above, 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 results-

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 - internal join

INNER JOIN combines two tables (table1and table2to create a new result table. The query will combine table1with the column values of2Each row of the first table is compared with the row of table

INNER JOIN is the most common default join type. You can choose to use the INNER keyword.

The syntax of INNER JOIN is as follows:-

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

To avoid redundancy and shorten the phrase, you can useUSINGThe expression declares the INNER JOIN condition. This expression specifies a list of one or more columns.

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

NATURAL JOIN withJOIN...USINGNatural similarity, just that it automatically tests whether the values of each column in the two tables are equal-

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

Based on the above table, 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 results-

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

OUTER JOIN - outer join

OUTER JOIN is an extension of INNER JOIN. Although the SQL standard defines three types of external joins: LEFT, RIGHT, and FULL, SQLite only supportsLEFT OUTER JOIN.

The conditions of the external join are the same as those of the inner join, represented by the ON, USING, or NATURAL keywords. The calculation method of the initial result table is the same. Once the main JOIN is calculated, the OUTER JOIN will retrieve all unconnected rows from one or two tables, fill them with NULL, and then append them to the result table.

The syntax of LEFT OUTER JOIN is as follows:-

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

To avoid redundancy and shorten the phrase, you can use a USING expression to declare the OUTER JOIN condition. This expression specifies a list of one or more columns.

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

Based on the above table, you can write an inner 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 results-

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