1

Table1

Id Name  DemoID
1  a      33
2  b      44
3  c      33
4  d      33
5  e      44

Table2

Id DemoID IsTrue
11  33     1  
12  44     1

Now we can find which DemoID is present in Table2 using below 2 queries using join and using sub-query, but which is more accurate/relevant for more number of records?

using join -

SELECT Table1.Id FROM Table1  Table1 join Table2 Table2 on Table1.DemoID  = Table2.DemoID 
WHERE Table2.IsTrue = 1

using sub-query -

SELECT Table1.Id  FROM Table1  Table1
WHERE Table1.DemoID  in (select Table2.DemoID from Table2 Table2)
6

but which is more accurate / relevant for more number of records ?

Neither and both. The optimizer is free to rewrite subqueries as joins.

For clarity, since you don't need any columns from Table2, I'd go with a third option and use EXISTS.

This is only a benefit for the reader in this case. In other cases, writing a join may result in unwanted row duplication, which will typically lead someone to add a DISTINCT or GROUP BY to remove them, which are unnecessary with EXISTS or IN...

Of course, it's different with NOT IN. If NULLs exist in the relation, you'll get an empty result set. This can be quite confusing to beginning query writers.

As a style choice, I go with EXISTS or NOT EXISTS when I'm referencing a subquery, and IN/NOT IN when I have a list of literal values.

Hope this helps!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.