Here we will learn SQLite left outer join with example and how to use SQLite left outer join with multiple tables to return all the records from left table and matching records from another table with example.
In SQLite, by using Inner Join we can get only matching rows from multiple tables based on the conditions defined in select statements. If we use SQLite Outer Join it will select matching rows from multiple tables same as Inner Join and some other rows outside of the relationship.
In simple terms we can say SQLite OUTER JOIN is an addition of INNER JOIN. Generally we have three types of Outer Joins in SQL standard those are LEFT, RIGHT and FULL Outer Joins but SQLite supports only LEFT OUTER JOIN.
Following is the syntax of using SQLite Left Outer Join with Select statement.
SELECT t1.col1,t2.col2,...FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.col3=t2.col5;
In the above SQLite Left Outer Join, table1 is a left-hand table, and table2 is a right-hand table. Here the left outer join tries to match every row of table1 table with every row in table2 table based on the join condition (t1.col3 = t2.col5) and it returns matching rows from both the tables and remaining rows of table1 table that doesn't match with table2 table are also included in the result.
Following is the simple example of SQLite Left Outer Join with Select statement.
SELECT * FROM X LEFT OUTER JOIN Y ON X.x = Y.y;
In the above example, we applied Left Outer Join on X and Y tables. The Left Outer Join matches all the rows of the X table with all the rows in Y table based on the defined condition X.x = Y.y.
Following is the simple pictorial representation of SQLite LEFT Outer Join to understand easily.
If you observe above SQLite LEFT OUTER JOIN result it returned matched rows from both the tables (X, Y) and unmatched rows from left (X) table.
Now we will see the example of SQLite Left Outer JOIN with a 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 the dept_master table execute the 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 the following queries to create and insert some data in the 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 (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', 49900,3),
(9,'Shwets','Jariwala',19400,2);
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
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35000 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 49900 3
9 Shwets Jariwala 19400 2
Now write SQLite query like as shown following to use Left Outer JOIN with a select statement.
SELECT d.dept_id,dept_name,emp_id,first_name
FROM dept_master d
LEFT OUTER JOIN
emp_master e ON d.dept_id=e.dept_id;
If you observe above SQLite Left Outer Join example we defined Left Join condition on the dept_id column of tables.
Now we will run and check the output that will be like as shown following.
dept_id dept_name emp_id first_name
---------- ---------- ---------- ----------
1 Admin 6 Sonal
2 Sales 4 Jagruti
2 Sales 2 Shweta
2 Sales 9 Shwets
2 Sales 7 Yamini
3 Quality Co 8 Khyati
3 Quality Co 5 Shweta
3 Quality Co 3 Vinay
4 Marketing
Here dept_master is a left-hand table so all the rows of the dept_master table included in the result even though rows are unmatched and it included only matched rows of emp_master table.
In emp_master table, we don't have any employee for the marketing department so in result emp_id and first_name are null.
This is how we can use SQLite Left Outer Join in our queries based on our requirements.