DbSchema | How to Implement JOINS in SQLite?
Table of Contents
- Introduction
- Prerequisites
- What are JOINS
- Types of JOINS
- Advantages and Limitations of using JOINS
- How to Implement JOINS in sqlite3
- How to Implement JOINS in DbSchema
- Conclusion
- 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?](/2023/06/02/sqlite/create-db/)
**_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?](/2023/06/03/sqlite/create-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 alt >](/img/blog/postgres/cross-join.png)
### Cross Join
```sql
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
Open your project.
Go to the menu bar, and click on __Data
-> __Relational Data Browse
.Click on __Add Table to Layout
button, select __Orders
and __Customerstables and click
OK`. The tables will be displayed on the canvas.Drag and drop the
CustomerID
column name fromOrders
table over theCustomerID
column inCustomers
table. This will create arelationship line
between the two tables indicating a JOIN.On the right panel, you can see
Filter
,Sort
,Group by
,Paging
tabs. You can add filters or sorting, as needed.Click on the SQL button to view the
SQL Query
generated for the JOIN. The generated SQL will be anINNER JOIN
by default.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.