SQL where equal to expression

Question

Can I do like this

select * 
from tableA JOIN 
tableB ON tableA.id=tableB.id 
where tableB.someId = select id from otherTable where anotherId = 1

I have 2 where, possible?

Raging Bull · Accepted Answer · 2014-05-05T17:08:34.627

6

You can use = when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN or EXISTS:

Using IN:

select * 
from tableA JOIN 
tableB ON tableA.id=tableB.id 
where tableB.someId IN (select id 
                    from otherTable 
                    where anotherId = 1)

IN determines whether a specified value matches any value in a subquery or a list.

Read more here.

USING EXISTS:

select * 
from tableA JOIN 
tableB ON tableA.id = tableB.id 
where EXISTS (select id 
      from otherTable 
      where anotherId = 1
      and tableB.someId = otherTable .id)

edited May 05 '14 at 17:08

answered Apr 25 '14 at 13:53

Raging Bull

18,593
13
50
55

You don't *have to use * `in`. In fact, `exists` can have better performance. `in` is a totally reasonable option though. – Gordon Linoff Apr 25 '14 at 13:57

score 1 · Answer 2 · edited May 23 '17 at 11:43

You could use the IN Clause:

select * 
from tableA JOIN 
tableB ON tableA.id = tableB.id 
where tableB.someId IN (select id 
                        from otherTable
                        where anotherId = 1)

You could also use the EXISTS Condition:

select * 
from tableA JOIN 
tableB ON tableA.id = tableB.id 
where EXISTS (select id 
              from otherTable ot
              where anotherId = 1
              and tableB.someId = ot.id)

= would also work fine, if the subquery returned a single value.

Difference between EXISTS and IN

score 0 · Answer 3 · answered Apr 25 '14 at 13:55

0

select * 
from tableA 
JOIN tableB ON tableA.id=tableB.id 
join otherTable ON tableb.id = othertable.id 
where otherTable.anotherId = 1

answered Apr 25 '14 at 13:55

NimChimpsky

46,453
60
198
311

This doesn't necessarily do the same thing, because the id could be duplicated in `othertable`. – Gordon Linoff Apr 25 '14 at 13:57
@GordonLinoff which would mean what ? – NimChimpsky Apr 25 '14 at 13:59
1

@NimChimpsky: That means with n matching records in otherTable you get n times as many result rows. Only when only one record matches, then you get the same number of rows as you would with IN or EXISTS. – Thorsten Kettner Apr 25 '14 at 14:09

SQL where equal to expression

3 Answers3