Using clause with left joins in PostgreSQL

Question

I have sql query that looks like this:

DELETE FROM survey_subjects WHERE id IN (
  SELECT ss.id FROM survey_subjects ss
    LEFT JOIN survey_instances si ON si.survey_subject_id = ss.id
    LEFT JOIN survey_packs sp ON si.survey_pack_id = sp.id
    LEFT JOIN school_packs scp ON sp.school_pack_id = scp.id
    LEFT JOIN survey_orders so ON scp.survey_order_id = so.id AND so.administration_id = <administration_id>
  WHERE ss.administration_id = <administration_id> AND si.id IS NULL
);

I was thinking how can I make this query more readable. I was thinking about using USING clause from PostgreSQL but it will not work for left joins. Is there any way to make it more readable?

using will of course work for left joins. But: using is only applicable if the join columns have the same name in both tables - which is clearly not the case for your tables. — user330315
– user330315, Commented Nov 3, 2015 at 14:51

Gordon Linoff · Accepted Answer · 2015-11-03 14:55:20Z

2

I think you should simplify the logic to this:

DELETE FROM survey_subjects WHERE id IN (
  SELECT ss.id 
  FROM survey_subjects ss LEFT JOIN
       survey_instances si
       ON si.survey_subject_id = ss.id
  WHERE ss.administration_id = <administration_id> AND si.id IS NULL
);

The additional joins are not doing anything. They are not filtering the data (because the joins are left joins). They are not affecting either column in the WHERE clause, nor the column in the SELECT.

answered Nov 3, 2015 at 14:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using clause with left joins in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related