Simplifying and reducing the cost of an anti-join query

Could yo开发者_开发知识库u please help me in simplifying and reducing the cost of the below query?

I tried making it a co-related subquery with NOT EXISTS but it didn't give me any output.

Please note that the table in both main and inner query is the same 'table_1".

SELECT *
FROM Table_1 A
WHERE A.Col1              = 'abcd'
AND (A.Col2, A.Col3) NOT IN
  (SELECT Col2,
    Col3
  FROM Table_1 B
  WHERE (B.Col4 IN (1,2,3)
  And B.Col5    In ('x','y'))
  OR (B.Col4     = 1 AND B.Col5     = 'z' AND B.Col6     = 'f')
  ))

Thanks in advance, Savitha

---

Usually its a lot about trial and error. In addition to using the not exists and outer join, since this is a self-join, it should reduce down to a single table...

SELECT *
FROM Table_1
WHERE col1 = 'abcd'
AND NOT
   (col4 IN (1,2,3)
      AND col5 IN ('x','y')
)
AND NOT (col4=1
  AND col5='z'
  AND col6='f'
);

Or you could try using a MINUS...

SELECT *
FROM Table_1
WHERE col1 = 'abcd'
MINUS
SELECT *
FROM Table_1
WHERE col1 = 'abcd'
AND ((col4 IN (1,2,3)
      AND col5 IN ('x','y'))
   OR (col4=1
      AND col5='z'
      AND col6='f')  
);

The most efficient solution will depend on the spread of the data.

but it didn't give me any output

Are you sure there are matching rows?

---

Try the following to see if it helps:

SELECT A.*
  FROM (SELECT *
          FROM Table_1
           WHERE A.Col1 = 'abcd') A
LEFT OUTER JOIN (SELECT Col2, Col3, primary_key_column
                   FROM Table_1
                   WHERE (B.Col4 IN (1, 2, 3) AND
                          B.Col5 IN ('x', 'y')) OR
                         (B.Col4 = 1 AND
                          B.Col5 = 'z' AND
                          B.Col6 = 'f')) B
  ON (B.Col2 = A.Col2 AND B.Col3 = A.Col3)
WHERE B.primary_key_column IS NULL;

You should have indexes on TABLE1(Col1), TABLE1(Col2, Col3) and on the primary key column of TABLE1, whatever it might be.

Share and enjoy.

---

Cool answer. Shouldn't the following be the same if indexes are used

SELECT A.*
  FROM Table_1 A
LEFT OUTER JOIN (SELECT Col2, Col3, primary_key_column
                   FROM Table_1
                   WHERE (B.Col4 IN (1, 2, 3) AND
                          B.Col5 IN ('x', 'y')) OR
                         (B.Col4 = 1 AND
                          B.Col5 = 'z' AND
                          B.Col6 = 'f')) B
  ON (B.Col2 = A.Col2 AND B.Col3 = A.Col3)
WHERE A.Col1 = 'abcd'
  and B.primary_key_column IS NULL;