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?
Add a comment |
3 Answers
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)INdetermines 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)
1 Comment
Gordon Linoff
You don't *have to use *
in. In fact, exists can have better performance. in is a totally reasonable option though.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.
Comments
select * from tableA JOIN tableB ON tableA.id=tableB.id join otherTable ON tableb.id = othertable.id where otherTable.anotherId = 1 3 Comments
Gordon Linoff
This doesn't necessarily do the same thing, because the id could be duplicated in
othertable.
NimChimpsky
@GordonLinoff which would mean what ?
Thorsten Kettner
@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.