❮ Sqlite Attach Database Sqlite Pragma ❯

SQLite `Join`

The Join clause in SQLite is used to combine rows from two or more tables in a database. A JOIN is a method to combine fields from two tables by using values common to each.

SQL defines three main types of joins:

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

Before we proceed, let's assume we have two tables, COMPANY and DEPARTMENT. We have already seen the INSERT statements used to populate the COMPANY table. Now, let's assume the COMPANY table has the following records:

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

Here 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 records in the DEPARTMENT table:

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

Cross Join - CROSS JOIN

A 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 result table will have x*y rows. Due to the potential for very large tables with Cross Join (CROSS JOIN), caution must be exercised and they should only be used when appropriate.

Cross Join operations return the Cartesian product of the sets of rows from the joined tables, resulting in a number of rows equal to the product of the number of rows that qualify from the first table and the number of rows that qualify from the second table.

Here is the syntax for a Cross Join (CROSS JOIN):

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

Based on the tables above, 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 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 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 Based on the table 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 query above will produce the following results:

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

❮ Sqlite Attach Database Sqlite Pragma ❯

SQLite Join

Cross Join - CROSS JOIN

SQLite `Join`