SQLite Joins

SQLite is a server-less database engine and it is written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main motive for developing SQLite is to overcome the use of complex database engines like MySQL etc. It has become one of the most popularly used database engines used in Television, Mobile Phones, web browsers, and many more. It is written simply so that it can be embedded into other applications.

In this article we will learn about the Joins in SQLite, and how it works, and also along with that, we will be looking at different types of joins in SQLite in a detailed and understandable way with examples.

SQLite JOINS

It is used to join two tables by using the common field in both of the tables. SQLite Joins' responsibility is to combine the records from two tables. Joins can only performed on the table if they have at least one column in common and based on that column we will be combining two tables using joins. One table must contain a column that is a reference for the other table and then only we can perform the Joins.

For Example, we have two tables Teachers and Department then assume that if we have a common column as Id in both tables then we can use that column to join the two tables and the tables look like

If you don't know How to Create a Table in SQLite then refer to this. After inserting some data into tables, it Looks Like this

Teacher Table:

Departments Table:

SQLite Joins is of different types. Some are Defined Below:

• INNER JOIN
  
• LEFT OUTER JOIN (LEFT JOIN)
  
• CROSS JOIN

But however the RIGHT OUTER JOIN and FULL OUTER JOIN are not supported in SQLite. So, now let us try to learn more about the other three joins in the coming sentences.

Let's discuss the SQLite Joins one by one in a detailed and Simple way.

INNER JOIN in SQLite

INNER JOIN can be performed on the two tables. However there need to be one common column inorder to perform the INNER JOIN. It returns all the rows from the multiple tables if the condition that you have specified is met and the resulting rows forms a new table.

Actually INNER JOIN is one of the most common Join and it is optional to write the keyword as INNER JOIN because even without specifing also we can perform the INNER JOIN.

Syntax:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Example of INNER JOIN

SELECT t.name, d.dept
FROM Teachers as t
INNER JOIN department as d
on t.Id = d.Id;

Output:

Explanation: In the above Query, with the help of INNER JOIN we have fetched the respective columns fields data here we use id from both table as a Matching or same column.

LEFT OUTER JOIN in SQLite

Actually, the LEFT OUTER JOIN is the extension of the INNER JOIN and there are LEFT, RIGHT, and FULL Outer Join, whereas SQLite only supports the LEFT OUTER JOIN.

Left Outer Join returns all the rows from the left-side table that has specified in the ON condition and it displays only the rows that met the join condition from the other table.

Syntax:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column; 

Example of LEFT OUTER JOIN

SELECT t.name, t.salary, d.dept
FROM Teachers as t
LEFT OUTER JOIN department as d
on t.Id = d.emp_id;

Output:

Explanation:In the above Query, with the help of LEFT OUTER JOIN we have fetched the respective columns fields data here we use id from both table as a Matching or same column. It also return all the data from the left table i.e, Teachers Table.

CROSS JOIN in SQLite

CROSS JOIN is also konown as a Cartesian product. CROSS JOIN returns the combined result set with every row matched from the first table with the second table.

For example, if there are 5 rows in first table and 5 rows in second table, then the cartesian product of first and second row is 5 * 5 i.e 25 rows are retrieved.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example of CROSS JOIN

SELECT * 
FROM Teachers
CROSS JOIN department;

Output:

Explanation: In the above Query, we have fetched the data from the both tabes rows are multiplied and the count of number of rows is increased to 25 as the product is done(5 * 5).

Conclusion

SQLite Joins are used to combine the two different tables based on the common columns and these are more efficient to use. SQLite Joins do not support Right and Outer Joins. However INNER JOIN is nothing but the simple join and it is not mandatory to specify. It is one of the best practices to combine the multiple tables and I hope by the end of the article you will get to know about the SQLite Joins and the functionality of it.