CODEWITHSUNDEEP
sql
prabin
prabin

Reputation:

Changing IN to EXISTS in SQL

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?

Upvotes: 10

Views: 14527

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56745

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 )

Upvotes: 13

dmajkic
dmajkic

Reputation: 3488

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

Upvotes: 4

Eric
Eric

Reputation: 95153

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.

Upvotes: 18

Related Questions