DELETE with RETURNING clause in PostgreSQL - Step-by-Step Execution

Concept Flow - DELETE with RETURNING clause

Start DELETE command

↓

Identify rows matching condition

↓

Delete those rows from table

↓

RETURNING clause fetches deleted rows

↓

Output deleted rows as result

↓

End

The DELETE command finds rows matching a condition, removes them, and the RETURNING clause outputs the deleted rows as a result.

Execution Sample

PostgreSQL

DELETE FROM employees
WHERE department = 'Sales'
RETURNING id, name;

Deletes employees in the Sales department and returns their id and name.

Execution Table

Step	Action	Rows Matched	Rows Deleted	RETURNING Output
1	Start DELETE command	-	-	-
2	Find rows where department = 'Sales'	3 rows found	-	-
3	Delete matched rows	3 rows matched	3 rows deleted	-
4	RETURNING outputs deleted rows' id and name	-	-	[{"id": 2, "name": "Alice"}, {"id": 5, "name": "Bob"}, {"id": 7, "name": "Carol"}]
5	End of DELETE with RETURNING	-	-	-

💡 All rows matching condition deleted and returned, operation complete.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
rows_matched	0	3	3	3	3
rows_deleted	0	0	3	3	3
returning_output	[]	[]	[]	[{"id":2,"name":"Alice"},{"id":5,"name":"Bob"},{"id":7,"name":"Carol"}]	[{"id":2,"name":"Alice"},{"id":5,"name":"Bob"},{"id":7,"name":"Carol"}]

Key Moments - 3 Insights

Why do we see the deleted rows in the output?

Because the RETURNING clause tells PostgreSQL to output the deleted rows' specified columns after deletion, as shown in step 4 of the execution_table.

Does the DELETE command remove rows before or after RETURNING outputs them?

Rows are deleted first (step 3), then RETURNING outputs the deleted rows (step 4), so the output reflects the rows just removed.

What happens if no rows match the condition?

No rows are deleted or returned; the RETURNING output will be empty, as no rows matched in step 2.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows were deleted at step 3?

A 3 rows

B 0 rows

C 5 rows

D 2 rows

💡 Hint

Check the 'Rows Deleted' column at step 3 in the execution_table.

At which step does the RETURNING clause output the deleted rows?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'RETURNING Output' column in the execution_table.

If the WHERE condition matched zero rows, what would be the value of 'rows_deleted' after step 3?

A 3

B 1

C 0

D Undefined

💡 Hint

Refer to the variable_tracker row for 'rows_deleted' and consider what happens if no rows match.

Concept Snapshot

DELETE FROM table_name
WHERE condition
RETURNING column1, column2;

- Deletes rows matching condition
- RETURNING outputs deleted rows
- Useful to see what was removed
- Works only in PostgreSQL

Full Transcript

This visual execution shows how the DELETE command with RETURNING clause works in PostgreSQL. First, the command starts and finds rows matching the WHERE condition. Then, those rows are deleted from the table. After deletion, the RETURNING clause outputs the specified columns of the deleted rows as the result. The execution table tracks each step, showing how many rows matched, were deleted, and what the RETURNING clause outputs. The variable tracker shows how key variables change after each step. Key moments clarify common confusions, such as why deleted rows appear in output and the order of deletion and returning. The quiz tests understanding of these steps. The snapshot summarizes the syntax and behavior for quick reference.