SQLite Join

Before we continue, let's assume there are two tables, COMPANY and DEPARTMENT. We have already seen the INSERT statements used to populate the COMPANY table. Now let's assume the list of records in the COMPANY 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 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 are the INSERT statements 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           Engineerin  2
3           Finance     7

Cross Join - CROSS JOIN

A 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*y rows. Since CROSS JOIN can potentially produce very large tables, it must be used with caution, only when appropriate.

The operation of a cross join returns the Cartesian product of all data rows from the two joined tables. The number of data rows returned equals 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        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

INNER JOIN creates a new result table by combining the column values of two tables (table1 and table2) based on the join predicate. The query compares each row in table1 with each row in table2, finding all matching pairs of rows that satisfy the join predicate. When the join predicate is satisfied, the column values of each matching pair of rows A and B are combined into a result row.

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

Below is the syntax for INNER JOIN:

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

To avoid redundancy and keep the wording shorter, you can useUSINGexpression to declare the INNER JOIN condition. This expression specifies a list of one or more columns:

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

NATURAL JOIN is similar toJOIN...USINGexcept that it automatically tests for equality between values in 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       Engineerin
7           James       Finance

Outer Join - OUTER JOIN

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

The way OUTER JOIN declares conditions is the same as INNER JOIN, using the ON, USING, or NATURAL keywords. The initial result table is computed in the same way. Once the primary join calculation is complete, OUTER JOIN merges in any unjoined rows from one or both tables, using NULL values for the outer join columns, and appends 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 wording shorter, you can useUSINGexpression 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 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       Engineerin
            Teddy
            Mark
            David
            Kim
7           James       Finance

Other extensions