Menu
Could some one please provide how to write following sql query using joins. I do not want use not in as well as if possible I would like to replace where condition as well.
SELECT d1.Short_Code FROM domain1 d1 WHERE d1.Short_Code NOT IN ( SELECT d2.Short_Code FROM Domain2 d2 ) I am using SQL Server 2008
341
Instead of an outer join or a complex OR operation (with the NOT IN clause) the optimizer should use the equivalent of an exclusion merge join between the two tables. the optimizer will build exactly same plan for both NOT IN and NOT EXISTS (as long as short_code is not nullable).
Overview. The SQL Server NOT IN operator is used to replace a group of arguments using the <> (or !=) operator that are combined with an AND. It can make code easier to read and understand for SELECT, UPDATE or DELETE SQL commands.
Equivalent Join For above subquery :SELECT l.id,l.name FROM tleft l LEFT JOIN tright r ON l.id = r. t_left_id WHERE r. t_left_id IS NULL; AND clause will be applied during the JOIN and WHERE clause will be applied after the JOIN .
This article:
may be if interest to you.
In a couple of words, this query:
SELECT d1.short_code FROM domain1 d1 LEFT JOIN domain2 d2 ON d2.short_code = d1.short_code WHERE d2.short_code IS NULL will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct.
You can also use this:
SELECT short_code FROM domain1 EXCEPT SELECT short_code FROM domain2 This is using neither NOT IN nor WHERE (and even no joins!), but this will remove all duplicates on domain1.short_code if any.
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
