Writing A Query With A NOT EXISTS Clause Without A Subquery For The NOT EXISTS

I was interested in writing a query for an application where I need to use a NOT EXISTS clause to check if a row exists. I am using Sybase but I would like to know if there is an

Solution 1:

You could write an anti-join using LEFT JOIN instead of an EXISTS:

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2
    ON  t2.Id = t1.Id
WHERE t2.Id IS NULL

But with the EXISTS operator, you must have a subquery.

Solution 2:

No, there is no way to use the EXISTS function in the way you are asking without a subquery.

Solution 3:

Not sure why you need the NOT EXISTS if you don't have a subquery to put in it, but having said that, is this what you're looking for?

SELECT * FROM mytable WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0)

The subquery will return nothing so the NOT EXISTS condition is always true.

Though that's the same as

SELECT * FROM mytable

Maybe you're using some kind of query building mechanism which automatically puts in the NOT EXISTS bit so you don't have a choice....

Solution 4:

If for whatever reason you can't use a NOT EXISTS, the suggestion from Bacon Bits to rewrite as an anti-left join is correct.

In my case, the reason for avoiding NOT EXISTS was defining an Oracle materialized view with REFRESH FAST ON COMMIT. Oracle doesn't allow this if there's a subquery in the WHERE clause. However, Oracle also doesn't allow ANSI-style joins in this case (at least up to 12.2).

If you're looking to define an Oracle materialized view with REFRESH FAST ON COMMIT, you'll also need to rewrite the query to remove ANSI-style joins, thus:

select t1.*
from TABLE1 T1, TABLE2 T2
where T1.ID (+)= T2.ID
and T2.ID is null;