How to join tables in SQLite

Sometimes when working with databases, we may need to join records from tables in a database. When that's the case, we can use the SQL JOIN statement that combines the specified fields using common values in each field.

There are three main types of SQLite joins.

1. The CROSS JOIN
2. INNER JOIN
3. LEFT OUTER JOIN

This tutorial will quickly walk you through these SQLite joins and show you how to join database records.

Let us start with the INNER JOIN as it simple and builds a foundation for us to understand other JOIN types.

1: The INNER JOIN

The INNER JOIN works by creating a new table by combining the values of the specified tables. It starts by comparing the row on each table and finding all matching pairs per the specified predicate. It then combines the matching pairs into a single row.

The general syntax for INNER JOIN is:

SELECT column(s) FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Let us look at a JOIN operation to illustrate how it works.

Suppose you have a table called users with fields: id, name, age, language-sample SQL query to create table is below:

CREATE TABLE "users" (
"id" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"age" INTEGER NOT NULL,
"language" TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
);

We also have another table called rows with the fields id, and role-SQL Query Provided below:

CREATE TABLE "Roles" (
"id" INTEGER NOT NULL,
"role" TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
)

Now we can run an SQLite INNER JOIN operation using the query shown below:

SELECT users.id, users.name, Roles.role FROM users INNER JOIN Roles ON users.id = Roles.id;

The above query will result in the table shown below:

2: The CROSS JOIN

The other type of SQL JOIN is the CROSS JOIN. This type matches each row from the first table to each row in the second table. Think of it as a Cartesian Product because the results are a set of rows from table 1 matched with each row on table 2. For example, if table1 has (a) rows and table2 has (b) rows, the resulting table will have a*b row.

NOTE: Be careful when using cross joins as they have the potential to result in enormous table sets.

The general syntax for a cross Join is:

SELECT column(s) FROM table1 CROSS JOIN table2;

Consider a cross join from the user's table with all rows of the roles table. Consider the SQL Query below:

SELECT * FROM users CROSS JOIN roles;

Running the above query will result in a table as shown below:

3: The LEFT OUTER JOIN

The final join we shall look at is the OUTER JOIN. The OUTER JOIN is an extension of the INNER JOIN. Like INNER join, we express it on conditions such as ON, NEUTRAL, and USING. It is also good to note that SQL defines three types of OUTER JOINS: left, right and full, but SQLite only supports LEFT JOIN.

The LEFT outer JOIN returns all rows from the LEFT table specified in the condition and only the rows from other tables where the joined condition is true.

Consider the following Query.

SELECT users.id, users.name, roles.role, users.language FROM users LEFT OUTER JOIN Roles ON users.id = Roles.id;

The above query will result in the table shown below:

Conclusion

This tutorial has illustrated how to perform SQL joins in SQLite and create new tables with modified information. It is good to keep experimenting with SQL JOINs as they come in very handy when working with databases.

Thank you for reading!