Use EXISTS to test related-row presence

By Batu · English technical notes

Use EXISTS when the question is whether at least one related row exists, rather than what values those rows contain. In the example, every project remains in the outer result. The correlated subquery checks whether any task has the same project_id as the current project. SQLite returns 1 for alpha and gamma, which have tasks, and 0 for beta, which has none.

The SELECT 1 inside EXISTS is conventional: the selected expression is not used to decide the result. SQLite specifies that EXISTS evaluates to 0 or 1 according to whether its subquery returns any rows, regardless of the row values or column count. SQLite’s expression documentation also explains that a correlated subquery can refer to columns from the outer query. Therefore, a task whose done value is 0 still establishes presence for alpha; the test is about a related row, not task completion.

Use another condition inside the subquery when the real question is narrower, for example AND t.done = 0 for an unfinished-task flag. Do not infer performance from this tiny in-memory example: indexing, table sizes, and the full query shape matter. The assertions establish the intended output for these fixture rows only. Python’s sqlite3 documentation describes the in-memory connection and result fetching used here. No newer API is required; Python 3.6+ is needed for f-string output. AI assistance disclosure: this article was drafted with AI assistance.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript(
    "CREATE TABLE project (id INTEGER PRIMARY KEY, name TEXT);"
    "CREATE TABLE task (project_id INTEGER, done INTEGER);"
)
con.executemany(
    "INSERT INTO project VALUES (?, ?)",
    [(1, "alpha"), (2, "beta"), (3, "gamma")],
)
con.executemany("INSERT INTO task VALUES (?, ?)", [(1, 0), (1, 1), (3, 1)])

has_task = con.execute(
    "SELECT p.name, "
    "EXISTS(SELECT 1 FROM task AS t WHERE t.project_id = p.id) "
    "FROM project AS p "
    "ORDER BY p.id"
).fetchall()

assert has_task == [("alpha", 1), ("beta", 0), ("gamma", 1)]
print(f"has_task={has_task}")
con.close()

has_task=[('alpha', 1), ('beta', 0), ('gamma', 1)]