YouTip

Join clause in SQLite 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 defines three major types of joins:

• Cross Join - CROSS JOIN
• Inner Join - INNER JOIN
• Outer Join - OUTER JOIN

Before we proceed, let us consider two tables COMPANY and DEPARTMENT. We have already seen the INSERT statement to populate the COMPANY table. Now let us assume that the records in the COMPANY table are 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

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

Below is the INSERT statement 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 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 every row of the first table with every row of the second table. If the input tables have x and y rows, respectively, the resulting table will have x*y rows. Because cross joins have the potential to create very large tables, they must be used carefully and only when appropriate.

The cross join operation returns the Cartesian product of the data rows from the two joined tables, returning a number of data rows equal to the number of data rows in the first table that meet the query conditions multiplied by the number of data rows in the second table that meet the query conditions.

Below is the syntax for CROSS JOIN:

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

Based on the tables above, we 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	Finance

Inner Join - INNER JOIN

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 that satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Inner Join is the most common type of join and is the default type of join. The INNER keyword is optional.

Below is the syntax for INNER JOIN:

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

To avoid redundancy and keep the phrasing short, the INNER JOIN condition can be declared using the USING expression. This expression specifies a list of one or more columns:

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

NATURAL JOIN is similar to 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...

Based on the tables above, we 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	Finance

Outer Join - OUTER JOIN

Outer Join is an extension of the Inner Join. While SQL standard defines three types of outer joins: LEFT, RIGHT, and FULL, SQLite only supports the Left Outer Join.

Outer Join is declared in the same way as the Inner Join, using the ON, USING, or NATURAL keywords. The initial result table is calculated in the same manner. Once the primary join is calculated, the Outer Join will merge in any unconnected rows from one or both tables, using NULL values for the columns of the Outer Join, and appending them to the result table.

Below is the syntax for LEFT OUTER JOIN:

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

To avoid redundancy and keep the phrasing short, the OUTER JOIN condition can be declared using the USING expression. This expression specifies a list of one or more columns:

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

Based on the tables above, we 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
3	Teddy	NULL
4	Mark	NULL
5	David	NULL
6	Kim	NULL
7	James	Finance