Spreadsheets & Data

Find Projects with No Tasks Using a SQL LEFT JOIN

How do I use a SQL LEFT JOIN to find projects that have no tasks?

Join projects to tasks with LEFT JOIN on project_id, then filter with WHERE t.task_id IS NULL. Every real task must have a non-null task identifier for this check to work. If task IDs can be null, use NOT EXISTS to check for missing matches.

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;

What you need

  • A SQL session in DuckDB, MySQL or PostgreSQL, connected to a scratch database
  • An empty database or separate working location for the sample tables
  • Non-null task identifiers for every real task

Use a SQL LEFT JOIN from projects to tasks on project_id, followed by WHERE t.task_id IS NULL, to find projects with no tasks. With this join, you retain projects that have no matching tasks and select them through the null check. Every real task must have a non-null identifier for that check to work; DuckDB’s outer join documentation explains how the join preserves unmatched projects.

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

Put projects on the left of the join, match tasks through the shared project identifier, and select projects with a null in the joined task identifier.

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;

Every real task needs a non-null task_id value. A task with a null identifier can pass this filter despite having a matching project. Check that assumption before using the result: null means missing or unknown data, so a null value alone does not prove that a row is missing.

Any matching task excludes a project from this query, whatever the task’s status. You get project IDs and names, with no explanation of why those projects lack tasks. I would use the label “projects with no task records,” rather than “neglected projects”: you cannot distinguish a planning placeholder from an overlooked project with these fields.

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

Create and populate the two sample tables in a scratch database before you run the missing-task query.

Use these fictional rows for the projects input:

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

Use these fictional rows for the tasks input:

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
  1. Open a DuckDB SQL session in an empty scratch database or a separate working location. Keep your existing project tables untouched.
  2. Submit the setup block below in full.
  3. Run the missing-task query with its join condition and null check unchanged.
  4. To adapt the query, substitute your table names and verify which columns identify projects and tasks.
CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL
);

CREATE TABLE tasks (
    task_id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    task_name VARCHAR(100) NOT NULL,
    status VARCHAR(100) 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');

Follow the relationship from tasks to projects. Each task refers to a project, but projects 102 and 105 have no task rows. Keep those projects in the input: you need to retain their records to report the absence of tasks.

Everything on this page is standard SQL. The setup, the LEFT JOIN query and the NOT EXISTS alternative run unchanged in DuckDB, MySQL and PostgreSQL, and return the same two projects.

Which projects should the missing-task query return?

You should get projects 102 and 105 from the sample query because the task input contains neither project ID.

project_id project_name
102 Cloudpickle Handbook
105 Jellybean Orbit Pilot

Count distinct project IDs when checking this result. The input has five projects, and the tasks refer to three distinct project IDs: 101, 103, and 104. That gives 5 - 3 = 2 empty projects, not 5 - 4 = 1. The four task rows do not belong to four different projects.

You will not see project 104 in the result, even though its only task is done. Marking a task complete changes its status; it does not remove the task record.

Add an explicit sort if you need to display the projects in a predictable order:

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;

I prefer to keep project_id in exports so a reviewer can identify a project without depending on a display name that someone might change. For a dashboard that uses the empty-project count, keep the denominator separate from task totals. The guide to calculating a completion rate without averaging percentages explains that reporting distinction.

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

With a LEFT JOIN, you get nulls in the task-side columns for unmatched projects, which lets you use a non-null task identifier to detect missing matches.

Use the aliases p for projects and t for tasks. Define a match between a task and a project with this condition:

ON t.project_id = p.project_id

DuckDB explains the preservation of unmatched left-side rows in its join documentation. Before filtering this sample, you get two joined rows for project 101 and one for each other project. The total is 2 + 1 + 1 + 1 + 1 = 6 rows.

Keep the two rows that lack task matches with this filter:

WHERE t.task_id IS NULL

After filtering out the four rows with real tasks, you have 6 - 4 = 2 rows. This is an anti-join pattern: you select parent records that have no matching children.

You do not need DISTINCT here. Project IDs are unique, and you get one row for each unmatched project. Before adding it to suppress repeated output, I would check the project input for duplicate records.

How do you fix null checks that return incorrect projects?

Check a guaranteed non-null task identifier with IS NULL, or choose NOT EXISTS if you have no reliable identifier to check.

A project appears even though it has a task

You can mistake incomplete task data for a missing task if you check an optional field. In a schema that permits unnamed tasks, for example, WHERE t.task_name IS NULL accepts existing tasks whose names are missing.

Use the task identifier for the check:

WHERE t.task_id IS NULL

Inspect the column’s nullability; its name does not guarantee a value. Use a correlated existence check if your data permits null IDs on real tasks:

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
);

With this query, you check for matching rows without depending on a value in task_id. DuckDB’s join documentation covers the same logic under anti joins.

No projects appear despite missing tasks

Do not use this filter to check for null:

WHERE t.task_id = NULL

You cannot get true from an ordinary comparison with null, even if you compare it to another null. DuckDB specifies IS NULL for this purpose in its null-handling documentation.

Use this comparison in its place:

WHERE t.task_id IS NULL

You cannot fix an equality comparison with null by changing the join or adding DISTINCT.

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

Place t.status = 'active' in the join’s ON clause to count active tasks as matches and ignore other statuses.

Do not put the status condition beside the null check like this:

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

For an unmatched project, you get null in the task-side status column. Under SQL null rules, that value cannot satisfy the active-status comparison. Remove the status condition if you want projects with no tasks at all.

To find projects with no active tasks, limit which task rows can match in the join:

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;

You get projects 102, 104, and 105 from this version. Projects 101 and 103 are the only projects with active tasks, leaving 5 - 2 = 3 qualifying projects.

I would give “no tasks” and “no active tasks” separate labels in the report. You include project 104 in the second result and exclude it from the first. Someone deciding which projects to review needs that distinction.

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

Choose another query if you need task counts, orphan tasks, or a check for missing tasks across a broader 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.

To count tasks, group the left-joined rows by project and count the non-null task identifiers. In project order, the sample gives you counts of 2, 0, 1, 1, and 0, for a total of four tasks. Do not use COUNT(*) for this: you would count the preserved row for an empty project as one.

For missing dates rather than missing project matches, follow the guide to filling missing days with zero in a SQL task-completion report. You can find other reporting problems that depend on the shape of the input on the spreadsheets and data topic page.

Review the scope of your task source before acting on the empty-project list. Excluding archived tasks from the joined input leaves a project with no matches if all its tasks are archived. I would state that limit in the report so readers do not assume the query checked task records outside its input.

Sources

Esc