SQLite: Joins

This SQLite post explains how to use SQLite JOINS (inner and outer) with syntax, visual illustrations, and examples.

Description

SQLite JOINS are used to retrieve data from multiple tables. A SQLite JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of SQLite joins:

• INNER JOIN (or sometimes called simple join)
• LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• CROSS JOIN

So let's discuss SQLite JOIN syntax, look at visual illustrations of SQLite JOINS, and explore some examples.

INNER JOIN (Simple Join)

Chances are, you've already written a statement that uses a SQLite INNER JOIN. It is the most common type of join. SQLite INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in SQLite is:

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

Visual Illustration

In this visual diagram, the SQLite INNER JOIN returns the shaded area:

The SQLite INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of a SQLite INNER JOIN:

SELECT employees.employee_id, employees.last_name, positions.title
FROM employees 
INNER JOIN positions
ON employees.position_id = positions.position_id;

This SQLite INNER JOIN example would return all rows from the employees and positions tables where there is a matching position_id value in both the employees and positions tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called employees with four fields (employee_id, last_name, first_name, and position_id). It contains the following data:

We have another table called positions with two fields (position_id and title). It contains the following data:

If we run the SQLite SELECT statement (that contains an INNER JOIN) below:

SELECT employees.employee_id, employees.last_name, positions.title
FROM employees 
INNER JOIN positions
ON employees.position_id = positions.position_id;

Our result set would look like this:

The row for employee_id 10003 from the employees table would be omitted, since the corresponding position_id does not exist in the positions table. The row for the position_id of 4 from the positions table would be omitted, since that position_id does not exist in the employees table.

Old Syntax

As a final note, it is worth mentioning that the SQLite INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT employees.employee_id, employees.last_name, positions.title
FROM employees, positions
WHERE employees.position_id = positions.position_id;

LEFT OUTER JOIN

Another type of join is called a SQLite LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the SQLite LEFT OUTER JOIN is:

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

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the SQLite LEFT OUTER JOIN returns the shaded area:

The SQLite LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example

Here is an example of a SQLite LEFT OUTER JOIN:

SELECT employees.employee_id, employees.last_name, positions.title
FROM employees 
LEFT OUTER JOIN positions
ON employees.position_id = positions.position_id;

This LEFT OUTER JOIN example would return all rows from the employees table and only those rows from the positions table where the joined fields are equal.

If a position_id value in the employees table does not exist in the positions table, all fields in the positions table will display as null> in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called employees with four fields (employee_id, last_name, first_name, and position_id). It contains the following data:

We have a second table called positions with two fields (position_id and title). It contains the following data:

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT employees.employee_id, employees.last_name, positions.title
FROM employees 
LEFT OUTER JOIN positions
ON employees.position_id = positions.position_id;

Our result set would look like this:

The row for employee_id 10003 would be included because a LEFT OUTER JOIN was used. However, you will notice that the title field for that record contains a null> value since there is no corresponding row in the positions table.

CROSS JOIN

Another type of join is called a SQLite CROSS JOIN. This type of join returns a combined result set with every row from the first table matched with every row from the second table. This is also called a Cartesian Product.

Syntax

The syntax for the SQLite CROSS JOIN is:

SELECT columns
FROM table1
CROSS JOIN table2;

Visual Illustration

In this visual diagram, the SQLite CROSS JOIN returns every row in table1 matched with every row in table2.

Example

Here is an example of a SQLite CROSS JOIN:

SELECT *
FROM positions 
CROSS JOIN departments;

This CROSS JOIN example would return all rows from the employees table matched with all rows from the positions table.

Let's look at some data to explain how CROSS JOINS work:

We have a table called positions with the following data:

We have a second table called departments with the following data:

If we run the SELECT statement (that contains a CROSS JOIN) below:

SELECT *
FROM positions 
CROSS JOIN departments;

Our result set would look like this:

Since the positions table has 4 rows and the departments has 2 rows, the cross join will return 8 rows (because 4x2=8). Each row from the positions table is matched with each row from the departments table.