SQLite Inner Join

  Total Views : 33
  Comments : 0
 
  Keywords : SQLite inner join with multiple tables example, How to use sqlite inner join with where clause example, SQLite inner join query with examples

Here we will learn sqlite inner join with example and how to use sqlite inner join with multiple tables to get only matching records with example

SQLite Inner Join

In SQLite INNER JOIN is used to combine and return only matching records from multiples tables based on the conditions defined in SQLite statements.

 

Generally, the SQLite Inner Join will return intersection elements of multiple sets i.e, only the common matching elements from multiple sets. Suppose if we have two sets {6, 4, 2, 3} and {8, 2, 5, 6} for these sets intersection will return {2, 6} because only {2,6} are common elements in both sets.

 

Following is the pictorial represention of two sets {6, 4, 2, 3} and {8, 2, 5, 6} intersection operation.

 

SQLite Inner Join Pictorial Representation with Example

 

If you observe above diagram we got only common elements in intersection same way SQLite inner join will return only common or matching rows from multiple tables.

 

In SQLite Inner Join is the most common and default type of JOIN. If we define JOIN in sqlite query automatically it will consider as a Inner Join.

Syntax of SQLite Inner Join

Following is the syntax of using SQLite inner join in Select statements.

 

SELECT t1.col1t2.col2, ... FROM table1 t1 [INNER] JOIN table2 t2 ON t1.col3=t2.col5;

Here INNER keyword is an optional to use and it returns only the rows which are common or matched in both tables. 

Example of SQLite Inner Join

Following is the simple example of using SQLite Inner Join with Select statement.

 

SELECT FROM X JOIN Y ON X.x = Y.y;

If you observe above SQLite Inner Join example, we are joining two tables X and Y. The Inner join will match every row of table X with every row of table Y based on the join condition (X.x = Y.y) and return only matching rows from both the tables.

 

Following is the simple pictorial representation of using SQLite Inner Join with two tables.

 

SQLite Inner Join Pictorial Representation with Example

Like as shown in above diagram INNER JOIN returns only matched rows from X and Y tables.

SQLite Inner Join Example2

Now we will see the example of SQLite INNER JOIN with database table. We need create two tables dept_master and emp_master and need to insert some data for that use following queries.

 

To create and insert some data in dept_master table execute following query.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control');

('Marketing');

Once we create and insert data execute following query to see the data of dept_master table.

 

sqlite> SELECT * FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute following queries to create and insert some data in emp_master table.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

Now write SQLite query like as shown following to use INNER JOIN with select statement.

 

SELECT d.dept_iddept_nameemp_idfirst_name

FROM dept_master d

JOIN emp_master e ON d.dept_id e.dept_id;

If you observe above SQLite INNER JOIN query we are joining dept_master and emp_master tables and applied Inner Join on dept_id column to get only employees whose dep_id equals with dept_id in department table. Now we will run and see the result of SQLite Inner Join example.

 

Following is the result of SQLite INNER JOIN example.

 

dept_id     dept_name   emp_id      first_name

----------  ----------  ----------  ----------

1           Admin       1           Honey

2           Sales       2           Shweta

3           Quality Co  3           Vinay

2           Sales       4           Jagruti

3           Quality Co  5           Shweta

1           Admin       6           Sonal

2           Sales       7           Yamini

3           Quality Co  8           Khyati

Here in above example, there is no employee from Marketing deparment that's the reason marketing deparment records are not included in the result.

 

This is how we can use Inner Join in our SQLite queries to get records from tables based on our requirements.

 

PREVIOUS

SQLite Joins
 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User