DELETE Statements

WHERE Chooses Rows

WHERE TRUE rows are chosen for removal.

highlighted = computed this step

WHERE chooses rows

DELETE starts by marking which rows have TRUE WHERE results.

WHERE before remove\text{WHERE before remove}

Read WHERE results

WHERE removes 1 row and has 0 UNKNOWN result here.

removed=1\text{removed}=1

DELETE examples are tiny finite row removals; SQL dialect completeness, joins in DELETE, subqueries, triggers, constraints, transactions, locks, indexes, optimizer behavior, performance, and product behavior are out of scope.

WHERE chooses rows: base rowsidownerstatuspoints1Adaopen102Benopen203Cydone154DeeNULL5 WHERE chooses rowssourcevaluecompareTotruthremoved012FALSEno122TRUEyes232FALSEno342FALSEno Rows removedsourcerowreason1(2, Ben, open, 20)WHERE_TRUE Rows after DELETEidownerstatuspoints1Adaopen103Cydone154DeeNULL5 DELETE factsfactvaluebaseRowCount4removedRowCount1keptRowCount3finalRowCount3whereUnknownCount0deleteStatusremoved_rowswhereKindeq

Only TRUE removes

FALSE and UNKNOWN rows stay in the table.

TRUE rows only\text{TRUE rows only}

Summary

DELETE removes rows only after WHERE chooses them.

chosen rows\text{chosen rows}