Changing IN to EXISTS in SQL

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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 (b) 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 internal column:

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