articles

Home / DeveloperSection / Articles / Use of JOIN clause in SQLite Database

Use of JOIN clause in SQLite Database

Tarun Kumar4122 22-Sep-2015

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

 


Updated 13-Dec-2017

Leave Comment

Comments

Liked By