Use of JOIN clause in SQLite Database

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:

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

 

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:

ID	DEPT	EMP_ID
1	Business Management	1
2	Finance	4
3	Marketing and Retail	7
4	Engineering	2

 

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:

EMP_ID	NAME	DEPT
1	PREETI	Business Management
4	PREETI	Finance
7	PREETI	Marketing and Retail
2	PREETI	Engineering
1	PRABHAT	Business Management
4	PRABHAT	Finance
7	PRABHAT	Marketing and Retail
2	PRABHAT	Engineering
1	REENA	Business Management
4	REENA	Finance
7	REENA	Marketing and Retail
2	REENA	Engineering
1	PINKI	Business Management
4	PINKI	Finance
7	PINKI	Marketing and Retail
2	PINKI	Engineering

 

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:

ID	NAME	DEPT
1	AJAY	Business Management
4	SUBHASH	Finance
7	PRABHAT	Marketing and Retail
2	VIJAY	Engineering

 

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:

ID	NAME	DEPT
1	AJAY	Business Management
2	VIJAY	Engineering
	VINAY	NULL
4	SUBHASH	Finance
	SURESH	NULL
	PREETI	NULL
7	PRABHAT	Marketing and Retail
	REENA	NULL
	PINKI	NULL