Concepts / Understanding WHERE Clauses: Filtering Rows

Understanding WHERE Clauses: Filtering Rows

A WHERE clause is the filter that tells DELETE which rows should be removed and which rows should remain untouched.

  • Programming
Interactive lab

Try it: INSERT, UPDATE and DELETE

How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.

How it works

  1. The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
  2. INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
  3. UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
  4. UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
  5. cur.rowcount is the number of rows inserted, changed or removed.
  6. The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.

Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.

Educational simulation

Loading the simulation…

Why Deletion Needs Care

Deleting data is different from inserting or updating it. Inserting adds a row, and updating changes an existing value. Deletion removes rows, and recovering them typically requires restoring from a backup. Because deletion can be difficult to reverse, the condition that identifies the rows must be checked carefully.

Key point

A WHERE clause is the filter that tells DELETE which rows should be removed and which rows should remain untouched.

What do you think happens?

A Track table contains two rows whose title is My Way. What happens when the statement deletes rows whose title equals My Way?

  • Only the first matching row is deleted

  • Every row in the table is deleted

  • Both matching rows are deleted and other rows remain

  • No rows are deleted until the table is recreated

Reveal answer
Answer:

Both matching rows are deleted and other rows remain

The WHERE condition is evaluated for each row. Every row whose title equals My Way matches the condition and is removed; rows with different titles do not match.

How the Filter Selects Rows

The database evaluates the WHERE condition independently for each row. For a condition such as title = 'My Way', a row titled Bohemian Rhapsody does not match, so it remains. A row titled My Way matches, so it is selected for deletion. The same evaluation happens for every other row, including any second row with the title My Way.

Bohemian Rhapsodytitle differsBohemian RhapsodyremainsMy Waytitle matchesImagineremainsImaginetitle differsMy Waytitle matchesremainsremains
Which rows does the database identify and remove when DELETE uses the condition title = 'My Way'?

The visual shows the essential behavior: matching rows are removed, while nonmatching rows survive. The condition does not mean “delete one row named My Way”; it identifies every row that satisfies the condition.

A Targeted DELETE

Worked example

Remove Tracks with a Specific Title

Remove every row from Track whose title is My Way, while leaving tracks with other titles untouched.

  1. 1
    Choose the table

    Track is the table from which rows will be removed.

  2. 2
    Add the filter

    The condition title = 'My Way' identifies the rows to delete.

  3. 3
    Evaluate each row

    Rows with the title My Way match the condition. Bohemian Rhapsody and Imagine do not match.

  4. 4
    Finalize the change

    Call commit() after DELETE so the pending deletion is finalized and the change is permanently removed from the database.

Result

The matching My Way rows are removed. Bohemian Rhapsody and Imagine remain.

sql

The DELETE statement identifies the table and the WHERE clause identifies the rows. The separate commit() call finalizes the change. The exact way a program invokes these operations depends on its database library, but the safety pattern remains the same: execute the targeted DELETE, then commit the pending change.

The Empty-Condition Hazard

DELETE FROM TrackWHERE title = 'MyWay'specific conditionMatching rowsother rows remainDELETE FROM Trackno conditionEvery rowtable becomes emptydeletes only matchesdeletes all rows
What is the difference between a targeted deletion and a deletion with no condition?
  • Common mistake

    Omitting the WHERE clause.

    What it looks like

    DELETE FROM Track

    Why it fails

    Without a condition, the database has no row filter. Every row matches the implicit instruction to delete all rows, so the table becomes empty.

    Do this instead

    Include a specific WHERE condition that identifies the intended rows.

  • Common mistake

    Skipping the verification step.

    What it looks like

    Executing DELETE immediately without first checking the condition.

    Why it fails

    A condition can identify different rows from the ones you intended to remove.

    Do this instead

    Test the WHERE clause with SELECT first, then execute DELETE after verifying the selected rows.

  • Common mistake

    Forgetting to call commit().

    What it looks like

    Executing DELETE and ending the operation without finalizing the transaction.

    Why it fails

    In many database systems, DELETE initially marks rows for deletion within a transaction rather than immediately finalizing the removal.

    Do this instead

    Call commit() after DELETE unless the database library or framework handles committing automatically.

Checking Before Committing

  1. 1
    Write the intended WHERE condition.
  2. 2
    Test that condition with SELECT to verify which rows it identifies.
  3. 3
    Use the same condition in DELETE.
  4. 4
    Call commit() to finalize the deletion.
Matching rowsWHERE conditionPending deletiontransactionFinalized deletionafter commit()DELETEcommit()
How does a deleted row move from a pending transaction state to a permanently saved database state?

In many database systems, DELETE first marks matching rows for deletion within a transaction. Calling commit() tells the database to finalize pending changes, make the deletion visible to other users or programs accessing the database, and write the change to disk. Some libraries or frameworks commit automatically, but in many educational and professional contexts the program is responsible for calling commit() explicitly.

Apply the Pattern

EASY

A Track table contains rows with the titles Bohemian Rhapsody, My Way, Imagine, and a second My Way row. Describe which rows remain after executing DELETE FROM Track WHERE title = 'My Way', and explain why commit() is needed afterward.

Hints
  • Evaluate the condition title = 'My Way' separately for each row.
  • Rows with different titles do not match the condition.
  • The deletion is finalized by calling commit().
MEDIUM

Before running a DELETE statement, write the SELECT statement you would use to check which rows satisfy the same WHERE condition. Then explain what risk remains if the WHERE clause is omitted.

Hints
  • Use the same table and condition in SELECT that you plan to use in DELETE.
  • Without WHERE, every row in the table matches the deletion instruction.

Key Takeaways

  1. 1
    DELETE removes rows from a table.
  2. 2
    WHERE filters the rows that DELETE removes by evaluating a condition for each row.
  3. 3
    Omitting WHERE deletes every row in the table.
  4. 4
    Test the WHERE condition with SELECT before executing DELETE.
  5. 5
    Call commit() to finalize the pending deletion unless the database library handles commits automatically.

At a glance

  • DELETE removes rows, while WHERE determines which rows match the deletion.
  • A condition is evaluated independently for each row, so all matching rows are removed and nonmatching rows remain.
  • DELETE without WHERE removes every row in the table.
  • Use SELECT to verify the target rows before DELETE.
  • Call commit() to finalize the deletion in systems where the operation is transactional.

Related Topics

Related concepts

More concepts starting with U