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
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.
If you observe the 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 an Inner Join.
Following is the syntax of using SQLite inner join in Select statements.
SELECT t1.col1, t2.col2, ... FROM table1 t1 [INNER] JOIN table2 t2 ON t1.col3=t2.col5;
Here INNER keyword is optional to use and it returns only the rows which are common or matched in both tables.
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.
Like as shown in the above diagram INNER JOIN returns only matched rows from X and Y tables.
Now we will see the example of SQLite INNER JOIN with the database table. We need to 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 the following query to see the data of the 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 a select statement.
SELECT d.dept_id, dept_name, emp_id, first_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 department that's the reason marketing department 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.