CODEWITHSUNDEEP
sqlpostgresql
pc_pyr
pc_pyr

Reputation: 632

sql query with left join condition

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)

Upvotes: 1

Views: 86

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521609

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;

Upvotes: 1

Related Questions