SQLite: FROM Clause

This SQLite post explains how to use the SQLite FROM clause with syntax and examples.

Description

The SQLite FROM clause is used to list the tables and any joins required for the query in SQLite.

Syntax

The syntax for the FROM clause in SQLite is:

FROM table1
[ { INNER JOIN | LEFT [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

The tables used in the SQLite statement. The two tables are joined based on table1.column1 = table2.column1.

Note

• When using the FROM clause in a SQLite statement, there must be at least one table listed in the FROM clause.
• If there are two or more tables listed in the SQLite FROM clause, these tables are generally joined using INNER or OUTER joins, as opposed to the older syntax in the WHERE clause.

Example - With one table

It is difficult to explain the syntax for the SQLite FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM employees
WHERE last_name = 'Mark'
ORDER BY first_name DESC;

In this SQLite FROM clause example, we've used the FROM clause to list the table called employees. There are no joins performed since we are only using one table.

Example - Two tables with INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

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

This SQLite FROM clause example uses the FROM clause to list two tables - employees and positions. And we are using the FROM clause to specify an INNER JOIN between the employees and positions tables based on the position_id column in both tables.

Example - Two Tables with OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT products.product_name, inventory.quantity
FROM products
LEFT OUTER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_id = 1000;

This SQLite FROM clause example uses the FROM clause to list two tables - products and inventory. And we are using the FROM clause to specify a LEFT OUTER JOIN between the products and inventory tables based on the product_id columns in both tables.