Home » SQLite Left Join

SQLite Left Join

Summary: in this tutorial, you will learn how to use SQLite LEFT JOIN clause to query data from multiple tables.

Introduction to SQLite LEFT JOIN clause

Similar to the INNER JOIN clause, the LEFT JOIN clause is an optional clause of the SELECT statement. You use the LEFT JOIN clause to query data from multiple related tables.

Suppose we have two tables: A and B.

  • A has m and f columns.
  • B has n and f columns.

To perform join between A and B using LEFT JOIN clause, you use the following statement:

SELECT
	a,
	b
FROM
	A
LEFT JOIN B ON A.f = B.f
WHERE search_condition;Code language: SQL (Structured Query Language) (sql)

The expression A.f = B.f is a conditional expression. Besides the equality (=) operator, you can use other comparison operators such as greater than (>), less than (<), etc.

The statement returns a result set that includes:

  1. Rows in table A (left table) that have corresponding rows in table B.
  2. Rows in the table A table and the rows in the table B filled with NULL values in case the row from table A does not have any corresponding rows in table B.

In other words, all rows in table A are included in the result set whether there are matching rows in table B or not.

In case you have a WHERE clause in the statement, the search_condition in the WHERE clause is applied after the matching of the LEFT JOIN clause completes.

See the following illustration of the LEFT JOIN clause between the A and B tables.