In this article we will learn about SQLite JOIN clause that combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.The SQLite Joins clause is used to combine records from two or more tables in a database.

Here we defines SQLite three major types of joins:

·         The CROSS JOIN

·         The INNER JOIN

·         The OUTER JOIN

Now, at first we create two tables EMPLOYEE and DEPARTMENT.

For creating EMPLOYEE table use the following definition:

CREATE TABLE EMPLOYEE
(
    ID INT PRIMARY KEY NOT NULL,
    NAME CHAR(50) NOT NULL,
    AGE INT(3) NOT NULL,
    ADDRESS CHAR(50) NOT NULL,
    CONTACT CHAR(11) NOT NULL
);

Here is the code for INSERT data into EMPLOYEE table:

INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (1, 'AJAY', 32, 'CalCUTTA', 9811233234 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (2, 'VIJAY', 25, 'DELHI', 9314353234 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (3, 'VINAY', 23, 'VARANSI', 9112333264 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (4, 'SUBHASH', 25, 'PUNJAB', 7212545264 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (5, 'SURESH', 27, 'MUMBAI', 8276545774 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (6, 'PREETI', 22, 'RAEBARELI', 4256675777 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (7, 'PRABHAT', 24, 'ALLAHABAD', 7756875777);
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT )
VALUES (8, 'REENA', 44, 'MUMBAI', 2222683522 );
INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,CONTACT)
VALUES (9, 'PINKI', 45, 'LUCKNOW', 1133644555 );

Now, here is the list of records that are inserted in our EMPLOYEE table:

For creating DEPARTMENT table use the following definition:

CREATE TABLE DEPARTMENT
(
   ID INT PRIMARY KEY NOT NULL,
   DEPT VARCHAR(50) NOT NULL,
   EMP_ID INT NOT NULL
);

Here is the code for INSERT data into DEPARTMENT table:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'Business Management', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Finance', 4 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Marketing and Retail', 7 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (4, 'Engineering', 2 );

Now, here is the list of records that are available in our DEPARTMENT table:

CROSS JOIN : The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

Following is the syntax of CROSS JOIN:

SELECT column_names,… FROM table1 CROSS JOIN table2

Now, here is the code for CROSS join are as follows:

sqlite> SELECT EMP_ID, NAME, DEPT FROM EMPLOYEE CROSS JOIN DEPARTMENT;

Above query will produce the following result:

INNER JOIN : An inner join requires each record in the two joined tables to have matching records, and is a commonly used join operation in applications but should not be assumed to be the best choice in all situations. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row.

Here is the syntax of INNER JOIN:

SELECT column_names,… FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Now, here is an example of INNER JOIN using both tables as follows:

sqlite> SELECT EMP_ID, NAME, DEPT FROM EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.ID = DEPARTMENT.EMP_ID;

Above query will produce the following result:

OUTER JOIN : The OUTER JOIN is an extension of the INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL but SQLite only supports the LEFT OUTER JOIN.

The OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER join will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table.

Here is the syntax of OUTER JOIN:

SELECT column_names,… FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Now, here is an example of OUTER JOIN using both tables as follows:

sqlite> SELECT EMP_ID, NAME, DEPT FROM EMPLOYEE LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID = DEPARTMENT.EMP_ID;

Above query will produce the following result: