CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Using clause with left joins in PostgreSQL

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?

Upvotes: 0

Views: 232

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

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.

Upvotes: 2

Related Questions