WITH

Step by step with WITH

Module 7 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Build a query in two steps that are easy to read.

WITH names the first step, then the main query uses it like a table.

Next: Step 1: the counts

On your own New data, same idea

In a WITH step called times, count the loans per book_id. Then show the title and loans of the books borrowed more than once.

The database

9 rows

The database

8 rows

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How WITH works

WITH: name a step, then use it like a table

1. WITH works out a step and names it

WITH loan_counts AS (
  SELECT member_id,
    COUNT(*) AS loans
  FROM loans
  GROUP BY member_id
)

known as loan_counts

2. The main query reads it like a table

SELECT members.name,
  loan_counts.loans
FROM members
JOIN loan_counts
  ON members.member_id
   = loan_counts.member_id;

Each step is short and can be run on its own, so a long query reads top to bottom.

What does WITH do?

WITH loan_counts AS (SELECT ...) SELECT ... FROM loan_counts
It names a query, so the main query can use it like a table. The name only lasts for this one query.
Why use it?

To build a long query in short steps that read top to bottom. Each step can be run and checked on its own, a bit like helper columns in Excel.
Can I have more than one step?

Yes, separated by commas: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b. Later steps can use earlier ones.
Is it the same as a subquery?

It does the same job as a subquery in FROM, but with a name, at the top. Many people find it easier to read.
How is this like Excel?

Like a helper column or a LET formula: =LET(total, SUM(B2:B11), B2/total) names a step and reuses it.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

WITH (common table expressions)
Database	How	Note
SQLite (these lessons)	`WITH name AS (...)`	Since SQLite 3.8.3 (2014).
PostgreSQL	`WITH name AS (...)`
MySQL / MariaDB	`WITH name AS (...)`	Since MySQL 8.0. In 5.7, use a subquery in FROM.
SQL Server	`;WITH name AS (...)`	The statement before it must end with ;, so people often start WITH with one.
Oracle	`WITH name AS (...)`	Oracle calls it subquery factoring.
Sybase ASE	`Not available`	Use a subquery in FROM, or a temporary #table.

Know someone fighting with a spreadsheet? Send them this page.