SQL how to get data from 2 tables with condition

Question

I need to get ALL id1 where id3 = 100

I've tried

SELECT id1 FROM "table 1" WHERE id2 = (SELECT id2 FROM "table 2" WHERE id3='100');

But it can be more than 1 result for id2.. so ofcourse i got an error

How can I get this data??

use `IN` instead of `=` – Amir Elsaeed Nov 23 '21 at 10:56 — Amir Elsaeed, Nov 23 '21 at 10:56

score 4 · Answer 1 · answered Nov 23 '21 at 10:58

4

If I read correctly, a simple join should work here:

SELECT t1.id1
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.id2 = t1.id2
WHERE t2.id3 = 100;

answered Nov 23 '21 at 10:58

Tim Biegeleisen

score 2 · Accepted Answer · edited Nov 23 '21 at 11:17

2

SELECT id1 
FROM "table 1" 
WHERE id2 IN ( SELECT id2 
               FROM "table 2" 
               WHERE id3='100'
              );

edited Nov 23 '21 at 11:17

Ergest Basha

answered Nov 23 '21 at 10:54

lalala

1

On large data set `EXISTS` will perform faster than `IN` – Ergest Basha Nov 23 '21 at 11:18
1

@ErgestBasha That is 100% false: they are exactly the same thing in SQL Server. An `IN` query is transformed when parsing into a correlated semi-join, which is the exact same thing as an `EXISTS`. The optimizer can then further transform both into either a normal join, an aggregated join or a correlated apply. Either way, no difference at all. try it yourself, you will see no difference https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=856d34d44545684ef5a99876b58e5388 – Charlieface Nov 23 '21 at 17:14
Totally agree with Charlie here. Actually, @Charlieface, do you know the origins of this (or its opposite) superstition? The heavily knowledgable Aaron Bertrand states in this post that it used to be true pre-2008? https://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance – George Menoutis Nov 23 '21 at 18:03

2 Answers2