DbSchema | How to Implement JOINS in SQLite?

---

Sqlite • 07-Jun-2023

by Malik Umair Sajjad

---

Table of Contents

1. Introduction
2. Prerequisites
3. What are JOINS
4. Types of JOINS
5. Advantages and Limitations of using JOINS
6. How to Implement JOINS in sqlite3
7. How to Implement JOINS in DbSchema
8. Conclusion
9. References

Introduction

This article aims to provide a detailed explanation about JOINS in SQL, their usage, advantages, and limitations. Further, we delve into how to implement JOINS in sqlite3 and DbSchema with the help of a sample database.

Prerequisites

• Basic understanding of SQL and databases
• SQLite3 and DbSchema installed on your machine
• Familiarity with SQL JOINs

For installation and establishing connection you can read our article SQLite-How to create a database?

What are JOINS

In SQL, a JOIN clause is used to combine rows from two or more tables, based on a related column between them. It allows users to perform complex queries that can involve more than one table, enabling data from multiple tables to be combined into a single result set.

Types of JOINS

Here are the different types of JOINS:

Join Type	Description
INNER JOIN	Returns records that have matching values in both tables
LEFT (OUTER) JOIN	Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN	Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN	Returns all records when there is a match in either left or right table
CROSS JOIN	Returns the Cartesian product of rows from tables in the join

Advantages and Limitations of using JOINS

Advantages:

• They help in combining data from multiple tables.
• Can help to normalize databases by eliminating redundancy.
• Enhances the database search, retrieval, and sorting processes.

Limitations:

• JOINS can become complicated quickly, leading to complex queries.
• Improper use of JOINS can lead to performance issues as they require a lot of system resources.
• FULL OUTER JOINS are not directly supported in all database systems (e.g., SQLite).

How to Implement JOINS in sqlite3

Step 1: Open your SQLite database using sqlite3 in your command line:

sqlite3  

Step 2: Create a New Database

To create a new database, type the following command:

.open sampleDB.db  

To know more about creating a database you can read our article SQLite-How to create a database?

Step 3: Create tables and populate data

Let’s consider two tables: Orders and Customers and insert some data in them.

CREATE TABLE Customers(
   CustomerID int,
   Name text
);

INSERT INTO Customers(CustomerID, Name) VALUES (1, 'Bob');
INSERT INTO Customers(CustomerID, Name) VALUES (2, 'Alice');
INSERT INTO Customers(CustomerID, Name) VALUES (3, 'Tom');

CREATE TABLE Orders(
   OrderID int,
   CustomerID int,
   Product text
);

INSERT INTO Orders(OrderID, CustomerID, Product) VALUES (1, 3, 'Apples');
INSERT INTO Orders(OrderID, CustomerID, Product) VALUES (2, 1, 'Bananas');
INSERT INTO Orders(OrderID, CustomerID, Product) VALUES (3, 2, 'Grapes');

To know more about creating a table you can read our article SQLite-How to create a Table?

Sample Database

Orders Table:

OrderID	_CustomerID_	_Product_
1	3	Apples
2	1	Bananas
3	2	Grapes

Customers Table:

CustomerID	Name
1	Bob
2	Alice
3	Tom

Step 4 : Execute Joins:

Cross Join

SELECT * FROM Orders
CROSS JOIN Customers;

Result After Executing The Query:

Following result was obtained after implementing cross join on sample database:

OrderID	_CustomerID_	Product	CustomerID	_Name_
1	3	Apples	1	Bob
1	3	Apples	2	Alice
1	3	Apples	3	Tom
2	1	Bananas	1	Bob
2	1	Bananas	2	Alice
2	1	Bananas	3	Tom
3	2	Grapes	1	Bob
3	2	Grapes	2	Alice
3	2	Grapes	3	Tom

Inner Join

SELECT Orders.OrderID, Customers.Name, Orders.Product
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result After Executing The Query:

Following result was obtained after implementing inner join on sample database:

OrderID	Name	Product
1	Tom	Apples
2	Bob	Bananas
3	Alice	Grapes

Outer Join

SQLite does not support the syntax for FULL OUTER JOIN, but it can be simulated:

SELECT Orders.OrderID, Customers.Name, Orders.Product
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
UNION ALL
SELECT Orders.OrderID, Customers.Name, Orders.Product
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Result After Executing The Query:

Following result was obtained after executing above query on sample database:

OrderID	_Name_	Product
1	Tom	Apples
2	Bob	Bananas
3	Alice	Grapes

Note:

Please note that the output of this query remains the same as the INNER JOIN output because there are no Customers without an Order or Orders without a Customer in our sample data. If there were Customers without any Orders or vice versa, they would be included in this output.

How to Implement JOINS in DbSchema

1. Open your project.

2. Go to the menu bar, and click on Data -> Relational Data Browse.

3. Click on Add Table to Diagram button, select Orders and Customers tables and click OK. The tables will be displayed on the canvas.

4. Drag and drop the CustomerID column name from Orders table over the CustomerID column in Customers table. This will create a relationship line between the two tables indicating a JOIN.

5. On the right panel, you can see Filter, Sort, Group by, Paging tabs. You can add filters or sorting, as needed.

6. Click on the SQL button to view the SQL Query generated for the JOIN. The generated SQL will be an INNER JOIN by default.

7. Finally, click on the Execute SQL button to view the results.

For different JOIN types, you can manually edit the generated SQL. For example, for LEFT JOIN, replace INNER JOIN with LEFT JOIN in the query.

Visually Manage SQLite using DbSchema

DbSchema is a SQLite client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

In summary, JOINs are powerful tools in SQL, allowing for efficient interaction and data retrieval from multiple tables. The JOIN operation, while resource-intensive and complex in some scenarios, remains a pivotal component of the SQL toolkit. Being adept with different types of JOINS and their implementations across various systems like sqlite3 and DbSchema is a valuable skill for any data manipulator or analyst.

References

1. SQLite JOINS
2. DbSchema Documentation
3. SQLite Official Website