|QUESTIONNAME|
Delete based on a subquery
|QUESTION|
In our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking?
|QUERY|
delete from cd.members where memid not in (select memid from cd.bookings);
|ANSWER|
<p>We can use subqueries to determine whether a row should be deleted or not. There's a couple of standard ways to do this. In our featured answer, the subquery produces a list of all the different member ids in the cd.bookings table. If a row in the table isn't in the list generated by the subquery, it gets deleted.

<p>An alternative is to use a <i>correlated subquery</i>. Where our previous example runs a large subquery once, the correlated approach instead specifies a smaller subqueryto run against every row.
<sql>
delete from cd.members mems where not exists (select 1 from cd.bookings where memid = mems.memid);
</sql>

<p>The two different forms can have different performance characteristics. Under the hood, your database engine is free to transform your query to execute it in a correlated or uncorrelated fashion, though, so things can be a little hard to predict.
|HINT|
You can delete based on the output of a subquery
|SORTED|
1
|PAGEID|
cb420c69-04cc-4b82-ad27-12e9840420f1
|WRITEABLE|
1
|RETURNTABLE|
cd.members
