10

I have the following query:

select A,
       B 
  from table1 
 where A in (select c 
               from table 2
            )

But, now I need to change this query and use exists instead of in, and it should give the same results.

My tables look like the following:

table1            table2
A    B               c
------             -----
1    x               1
2    y               3
3    z               4
4    w               7
5    a
1    b

How do I use the exists function?

Doug Porter
  • 7,721
  • 4
  • 40
  • 55

3 Answers3

17

You need to match the two columns that will be used in the exists together:

select
    t1.a, t1.b
from
    table1 t1
where
    exists (select 1 from table2 t2 where t2.c = t1.a)

The reason why you have to do that, is because exists performs a semi-join on the table, and therefore, needs to have a join condition.

Eric
  • 92,005
  • 12
  • 114
  • 115
11

Changing the expression:

FROM Table1 WHERE a IN( SELECT c FROM Table2 )

To an EXISTS is a simple matter of:

  1. Add a WHERE on the end of the internal SELECT

    FROM Table1 WHERE a IN( SELECT c FROM Table2 WHERE )

  2. Move the external match column (a) into the internal SELECT's WHERE clause

    FROM Table1 WHERE  IN( SELECT c FROM Table2 WHERE a )

  3. Move the internal match column (c) to the WHERE clause, leaving a column placeholder (a constant or *):

    FROM Table1 WHERE  IN( SELECT * FROM Table2 WHERE a = c )

  4. Change the IN to EXISTS:

    FROM Table1 WHERE EXISTS( SELECT * FROM Table2 WHERE a = c )

  5. To be safe add the table name onto the external column:

    FROM Table1 WHERE EXISTS( SELECT * FROM Table2 WHERE Table1.a = c )
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
4

This will do it via direct inner join. 

 select 
    t1.a, t1.b
 from 
    table1 as t1 
       inner join table2 as t2 on t1.a=t2.c
dmajkic
  • 3,448
  • 1
  • 18
  • 24
  • Sometimes this is faster, sometimes it's not. If `table2` is significantly smaller than `table1`, often it's much faster to do `exists`. Get the execution plan for conclusive results. – Eric Aug 14 '09 at 17:12
  • 1
    @Eric: if "smart" sql server is used than it will act by the plan, but if sql optimization is limited (eg. on sqlite) than there is more likely that optimization will be simply turned off if there is IN, OR, EXISTS, etc. Prabic didn't tag specific server he is using. – dmajkic Aug 14 '09 at 19:07