DELETE with RETURNING clause in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DELETE statement do in SQL?

The DELETE statement removes rows from a table based on a specified condition.

beginner

What is the purpose of the RETURNING clause in a DELETE statement?

The RETURNING clause returns the deleted rows' data immediately after deletion, allowing you to see what was removed.

intermediate

Write a simple DELETE statement with RETURNING to remove users older than 30 and return their names.

DELETE FROM users WHERE age > 30 RETURNING name;

intermediate

Can the RETURNING clause be used with other SQL commands besides DELETE?

Yes, RETURNING can also be used with INSERT and UPDATE to return affected rows.

beginner

Why is RETURNING useful in applications?

RETURNING helps applications get immediate feedback on what data was changed without running a separate SELECT query.

What does the RETURNING clause do in a DELETE statement?

A Creates a backup of the table

B Prevents deletion of rows

C Updates rows instead of deleting

D Returns the deleted rows' data

The RETURNING clause returns the data of rows that were deleted.

Which SQL command can use the RETURNING clause?

A DELETE only

B CREATE only

C INSERT, UPDATE, DELETE

D SELECT only

RETURNING can be used with INSERT, UPDATE, and DELETE commands.

What will this query do? DELETE FROM orders WHERE id = 5 RETURNING *;

A Delete order with id 5 and return all its columns

B Delete all orders except id 5

C Return all orders without deleting

D Update order with id 5

It deletes the order with id 5 and returns all columns of that deleted row.

Is RETURNING clause standard SQL or PostgreSQL specific?

A PostgreSQL specific

B Standard SQL

C Only MySQL supports it

D Only Oracle supports it

RETURNING is a PostgreSQL-specific feature, not part of standard SQL.

Why might you prefer DELETE with RETURNING over DELETE followed by SELECT?

A It requires two queries

B It returns deleted data immediately in one query

C It is slower

D It does not delete rows

DELETE with RETURNING returns deleted rows immediately, avoiding a second query.

Explain how the DELETE statement with RETURNING clause works in PostgreSQL.

Think about deleting and seeing what was deleted at the same time.

Describe a real-life scenario where using DELETE with RETURNING would be helpful.

Imagine you want to confirm what you removed from a list.