psql delete using join and return values

Question

Hello I have this query:

DELETE FROM c_table as c
 USING 
 a_table as a,
 b_table as b
 WHERE 
 a.id = c.id 
 AND b.a_id = c.id
 RETURNING a.*, b.*;

but I have a problem, I need all the rows of table B that have the a_id, using this query I'm just returning the first one

I don't know how I could do this, could anyone help me?

would I need to use two queries? first delete and then select with join?

Answer 1

I need all the rows of table B that have the a_id

Indicates a 1:n relationship between table A and B, and a 1:1 relationship between C and A.

There is no join condition between table A and B in the USING clause, which makes it an unconditional CROSS JOIN (Cartesian Product). The WHERE conditions connect rows via table C, not sure the planner will optimize that perfectly. I'd rather join A and B directly to exclude redundant rows early.

Even then, the query proposes the same row in C for deletion multiple times (once for every related row in B). Reduce that to distinct c.id. One way is to use EXISTS instead of a JOIN.

And yes, first delete and then select with join:

WITH del AS (
 DELETE FROM c_table AS c
 USING a_table a
 WHERE c.id = a.id
 AND EXISTS (SELECT FROM b_table b WHERE b.a_id = c.id)
 RETURNING c_id
 )
SELECT b.*
FROM del d 
JOIN b_table b ON b.a_id = d.id;

Obviously, this only returns rows from B where the related row in C has been deleted.