SQLite Joins

SQLite Joins

Before we move on, let's assume that there are two tables, COMPANY and DEPARTMENT. W e've seen the INSERT statement used to populate the COMPANY table. Now let's assume that 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
);

Here's the INSERT statement that populates 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-connection - CROSS JOIN

CROSS JOIN matches each row of the first table to each row of the second table. I f the two input tables have x and y columns, respectively, the result table has x-y columns. Because cross-connections (CROSS JOIN) can produce very large tables, care must be taken when using them only when appropriate.

Here's the syntax for CROSS JOIN:

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

Based on the table above, we can write a cross-connection (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

Internal connection - INNER JOIN

INNER JOIN creates a new result table based on the connection predicate combining the column values of the two tables (table1 and table2). T he query compares each row in table1 to each row in table2 and finds matching pairs for all the rows that satisfy the connection predicate. When the connection predicate is satisfied, the column values of each matching pair of lines A and B are combined into a result row.

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

Here's the syntax for INNER JOIN:

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

To avoid redundancy and maintain shorter wording, you can use the USING expression 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 to JOIN... USING, but it automatically tests the equal values that exist between the values of each column in the two tables:

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

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

External connection - OUTER JOIN

Outer Join (OUTER JOIN) is an extension of INTERNAL JOIN. Although the SQL standard defines three types of external connections: LEFT, RIGHT, FULL, SQLite only supports left outer connections (LEFT OUTER JOIN).

Outer JOIN declares conditions in the same way as INTERNAL JOIN, using the ON, USING, or NATURAL keywords. T he original result table is calculated in the same way. Once the primary connection calculation is complete, outer joins (OUTER JOIN) are merged from any of the un connected rows in one or two tables, and the externally connected columns use NULL values to attach them to the result table.

Here's the syntax for left OUTER JOIN:

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

To avoid redundancy and maintain short wording, you can use the USING expression to declare an 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 table above, we can write an outer connection (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