sql query with left join condition

Question

Below is a SQL query:

SELECT ID, NAME FROM TABLE1 A
LEFT JOIN TABLE2 B
ON A.COLUMN1=B.COLUMN1
WHERE (
A.NAME = 'TEST'
OR
(NOT EXISTS (SELECT * FROM TABLE3 C 
WHERE C.COLUMN1=A.COLUMN2
AND C.COLUMN2=B.COLUMN2)
AND 
NOT EXISTS (SELECT * FROM TABLE4 D 
WHERE D.COLUMN1=A.COLUMN2
AND D.COLUMN2=B.COLUMN2)));

which does not seem to be supported currently, would there be a different approach for this?

Alternative that I have already tried, but gives different result:

A.COLUMN2 NOT IN (SELECT DISTINCT COLUMN1 FROM TABLE3)

Actually, this should be supported by Redshift AFAIK. What is the actual error message? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 3, 2021 at 11:40
That query is most certainly supported by Postgres - which DBMS are you really using? select version() will tell you. And what is the error message you get? — user330315
– user330315, Commented May 7, 2021 at 5:25

Tim Biegeleisen · Accepted Answer · 2021-05-03 11:47:50Z

1

You could try refactoring to use left anti-joins instead of the exists subqueries:

SELECT ID, NAME
FROM TABLE1 A
LEFT JOIN TABLE2 B
    ON A.COLUMN1 = B.COLUMN1
LEFT JOIN TABLE3 C
    ON C.COLUMN1 = A.COLUMN2 AND
       C.COLUMN2 = B.COLUMN2
LEFT JOIN TABLE4 D 
    ON D.COLUMN1 = A.COLUMN2 AND
       D.COLUMN2 = B.COLUMN2
WHERE
    A.NAME = 'TEST' AND
    C.COLUMN1 IS NULL AND
    D.COLUMN1 IS NULL;

answered May 3, 2021 at 11:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Bill Weiner Over a year ago

Tim Biegeleisen is correct you need to rewrite this query. There are 2 reasons for this. 1) It's a correlated sub-query and this isn't support by Redshift and even if this wasn't the case 2) WHERE NOT EXIST statements can be extremely inefficient on a clustered database.

Collectives™ on Stack Overflow

sql query with left join condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related