Find Projects with No Tasks Using a SQL LEFT JOIN

How do you find projects with no tasks using SQL LEFT JOIN?

Use a SQL LEFT JOIN from projects to tasks on project_id, then keep rows where the non-null task identifier is null in the joined result.

SELECT p.project_id, p.project_name FROM projects p LEFT JOIN tasks t ON t.project_id = p.project_id WHERE t.task_id IS NULL;

Real tasks must have non-null task_id values. A primary key on tasks.task_id enforces that requirement. Otherwise, a matched task with a null identifier could incorrectly qualify.

This query returns project IDs and names, not task details. It treats every matching task as evidence that a project is not empty, regardless of task status. An empty project may be a planning placeholder or paused effort; the result describes missing database records, not their cause.

How do you create the sample projects and tasks in DuckDB?

Create two DuckDB tables linked by project_id, with primary keys that make project and task identifiers unique and non-null.

The fictional projects input contains:

project_id	project_name
101	Moonberry Launch
102	Cloudpickle Handbook
103	Waffle Comet Site
104	Teacup Nebula Review
105	Jellybean Orbit Pilot

The fictional tasks input contains:

task_id	project_id	task_name	status
501	101	Draft moonberry outline	active
502	101	Review moonberry notes	done
503	103	Sketch waffle homepage	active
504	104	File teacup notes	done

Follow these numbered setup steps:

1. Open a DuckDB SQL session connected to a scratch database. Choose an empty database or separate working location to avoid table-name collisions. This setup does not drop existing tables.
2. Create and populate the tables. Submit the complete setup block below.
3. Run the missing-task LEFT JOIN query. Keep the join condition and null check unchanged when comparing results.
4. Adapt table names for your database. Confirm that the child project reference matches the parent identifier and that the checked child identifier cannot be null.

CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name VARCHAR NOT NULL
);

CREATE TABLE tasks (
    task_id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    task_name VARCHAR NOT NULL,
    status VARCHAR NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

INSERT INTO projects (project_id, project_name) VALUES
    (101, 'Moonberry Launch'),
    (102, 'Cloudpickle Handbook'),
    (103, 'Waffle Comet Site'),
    (104, 'Teacup Nebula Review'),
    (105, 'Jellybean Orbit Pilot');

INSERT INTO tasks (task_id, project_id, task_name, status) VALUES
    (501, 101, 'Draft moonberry outline', 'active'),
    (502, 101, 'Review moonberry notes', 'done'),
    (503, 103, 'Sketch waffle homepage', 'active'),
    (504, 104, 'File teacup notes', 'done');

The foreign key requires task references to point to existing projects. It does not require every project to have a task.

Which projects should the missing-task query return?

The sample missing-task query returns projects 102 and 105 because neither project has a corresponding row in tasks.

project_id	project_name
102	Cloudpickle Handbook
105	Jellybean Orbit Pilot

Project 101 has two tasks, and projects 103 and 104 each have one. Project 104 is excluded even though its task is done: completed tasks still count as tasks.

SQL does not guarantee display order without ORDER BY. For stable ordering, use:

SELECT p.project_id, p.project_name
FROM projects p
LEFT JOIN tasks t
    ON t.project_id = p.project_id
WHERE t.task_id IS NULL
ORDER BY p.project_id;

Keep project_id in exported results because project names can repeat or change.

Why does LEFT JOIN with IS NULL find projects with no tasks?

A LEFT JOIN followed by IS NULL finds projects with no tasks by preserving unmatched projects and selecting their null task-side identifiers. In logical query order, FROM projects p establishes projects as the starting set, because the report needs project records rather than task records. Next, LEFT JOIN tasks t uses ON t.project_id = p.project_id to match each project to its tasks. Unlike an inner join, this keeps projects without matches, filling their task-side columns with nulls. Then WHERE t.task_id IS NULL retains those unmatched rows; real tasks have non-null identifiers under the sample primary key. This checks absence directly without grouping rows or calculating task counts. Finally, SELECT p.project_id, p.project_name returns only the identifying project columns rather than task details. Projects 102 and 105 remain. No DISTINCT is needed: matched projects are removed, and each unmatched project contributes one row. The optional ORDER BY p.project_id makes the display order predictable.

Why does checking a task identifier for null find unmatched projects?

A LEFT JOIN preserves each unmatched project and fills its task-side columns with nulls, so checking a non-null task identifier distinguishes missing matches from real tasks.

The aliases p and t identify projects and tasks. This join condition defines matching rows:

ON t.project_id = p.project_id

A project with multiple tasks produces multiple joined rows before filtering. A project with no tasks produces one preserved row with null task-side values.

This filter retains only unmatched rows:

WHERE t.task_id IS NULL

The pattern is an anti-join: it selects parent records lacking matching children. Grouping and counting are unnecessary. With unique project IDs, DISTINCT is also unnecessary because matched projects disappear and each unmatched project contributes one row.

How do you fix null checks that return incorrect projects?

Fix incorrect missing-task results by checking a guaranteed non-null child identifier with IS NULL, rather than checking an optional field or comparing with = NULL.

Failure: An optional task field identifies existing tasks as missing

Symptom: A project appears even though it has a task.

In a schema that permits unnamed tasks, WHERE t.task_name IS NULL accepts real tasks with null names. The sample schema prohibits null task names, but other schemas may not.

Exact fix: Check the non-null task identifier:

WHERE t.task_id IS NULL

Confirm the constraint rather than trusting the column name. If real task IDs can be null and the schema cannot be corrected immediately, use NOT EXISTS:

SELECT p.project_id, p.project_name
FROM projects p
WHERE NOT EXISTS (
    SELECT 1
    FROM tasks t
    WHERE t.project_id = p.project_id
);

This alternative checks matching rows directly without depending on task_id.

Failure: Comparing with NULL removes all expected results

Symptom: No projects appear despite projects 102 and 105 having no tasks.

The incorrect filter is:

WHERE t.task_id = NULL

Ordinary equality with null evaluates to unknown, while WHERE retains only true conditions.

Exact fix: Replace = NULL with IS NULL:

WHERE t.task_id IS NULL

This correction follows SQL null-handling rules, not a DuckDB-specific workaround.

Where should a status filter go when finding projects with no active tasks?

Put t.status = 'active' inside the LEFT JOIN condition to find projects with no active tasks, while keeping WHERE t.task_id IS NULL as the missing-match check.

Failure: A WHERE status condition removes empty projects

Symptom: Adding a status condition makes empty projects disappear.

This filter cannot identify projects with no active tasks:

WHERE t.task_id IS NULL
  AND t.status = 'active'

An unmatched project’s task-side status is null, so the active-status comparison cannot be true.

Exact fix for no tasks at all: Remove the status condition.

Exact fix for no active tasks: Restrict matches inside ON:

SELECT p.project_id, p.project_name
FROM projects p
LEFT JOIN tasks t
    ON t.project_id = p.project_id
   AND t.status = 'active'
WHERE t.task_id IS NULL
ORDER BY p.project_id;

The sample returns projects 102, 104, and 105. Project 104 qualifies because its only task is done. Label this report “projects with no active tasks,” not “projects with no tasks.”

Parent-side filters, such as a project archive flag in an actual schema, can go in WHERE alongside the null check without rejecting unmatched task-side rows.

When should you avoid this missing-task LEFT JOIN query?

Avoid the missing-task query when you need task counts, orphan-task detection, or a task scope different from the joined source.

Requirement	Appropriate approach
Count tasks for every project	Use LEFT JOIN, grouping, and COUNT(t.task_id) with non-null task IDs.
Find tasks referencing nonexistent projects	Start from tasks and check for missing parents, or use NOT EXISTS against projects.
Find projects with no tasks whatsoever	Join the authoritative task source that includes all relevant tasks.

For counts, avoid COUNT(*): an empty project’s preserved row would count as one.

The sample foreign key prevents orphan task references, but imported data may lack that protection.

A view hiding archived tasks can make projects with only archived tasks appear empty. Use a filtered view only when its definition matches the report’s intended scope.