Which of these queries is more efficient?

Question

Which of these queries are more efficient?

select 1 as newAndClosed
    from sysibm.sysdummy1
    where exists (
        select 1 
            from items 
            where new = 1
        ) 
        and not exists (
            select 1 
                from status 
                where open = 1
        )


select 1 as newAndClosed
    from items
    where new = 1 
        and not exists (
            select 1 
                from status 
                where open = 1
        )

Answer 1

Look at the explain plan and/or profiler output. Also, measure it. Measure it using bind variables and repeated runs.

Answer 2

I would personally say the second query.

First, it queries the table Items directly and filter with the WHERE clause on a field of this table.

Second, it uses only one other subquery, instead of two.

In the end, the second example ends doing only two queries, when the first example does three.

Many queries will always be more expensive than less queries to manage for the database engine. Except if you perform EXISTS verifications instead of table joints. A joint is more expensive than an EXISTS clause.

Answer 3

I think the second one is faster because on contrary to the first one, the sysibm.sysdummy1 table does not need to parse

Answer 4

From a simplistic point of view I'd expect query 2 to run faster because it involves fewer queries, but as Hank points out, running it through a Profiler is the best way to be sure.

Answer 5

They will produce different results if items contains more than one element with new = 1. exists will only check first record which matches the condition. So I'd vote for first variant, if in actual query you don't have relation between items and status (as in your example).

P.S. Usually I use SELECT 1 WHERE 2==2 when I need only one result from nowhere. If I need more SELECT 1 UNION SELECT 2.

Answer 6

probably after query optimization both this queries are equal...