August 6, 2013 at 10:51 pm
Good question....
Thanks twin 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 6, 2013 at 11:42 pm
Most of time i try to avoid Exists clause. I will prefer join or sub query.
Good question 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
August 7, 2013 at 12:36 am
Good question, but the explanation could have been better.
You could have explained why the condition is similar to "WHERE 1 = 1"
Your query is as below
SELECT
ID, NAME
FROM @customer C
WHERE NOT EXISTS ( SELECT 1
FROM @Order WHERE ID = c.ID)
As you haven't specified the alias name for the left column in you WHERE clause in EXISTS checking, SQL Server checks if ID exists in the @Order table
Since, ID doesn't exist in the @Order table, it will now check in the outer table which is @customer
Your query then effectively becomes the below
SELECT
ID, NAME
FROM @customer C
WHERE NOT EXISTS ( SELECT 1
FROM @Order WHERE c.ID = c.ID)
c.ID will always be equal to itself unless it is NULL and hence the behavior similar to "WHERE 1 = 1"
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 1:05 am
Thanks for the comments guys.
@kingston Dhasian: your explanation is the correct one.
Actually this was a real time issue, when people just write queries in a hurry and forget to set the proper alias. 😎
Actually it was a real time issue happens with my colleague, he asked to check what is wrong with the query and when i told him the issue he was like :w00t:, He kept saying that SQL SERVER should return a error in this case 😀 ....
August 7, 2013 at 1:11 am
Danny Ocean (8/6/2013)
Most of time i try to avoid Exists clause. I will prefer join or sub query.Good question 🙂
sub query ? does that means in clause as well please look into below query
SELECT
ID, NAME
FROM @customer C
WHERE C.ID not in ( SELECT ID
FROM @Order )
Neeraj Prasad Sharma
Sql Server Tutorials
August 7, 2013 at 2:10 am
Danny Ocean (8/6/2013)
Most of time i try to avoid Exists clause. I will prefer join or sub query.Good question 🙂
Exists IS (or rather, uses) a subquery. If you mean that you prefer to use a subquery with IN rather than EXISTS, you're opening yourself up for another surprise - NULL values don't behave as many people expect in a NOT IN. And you cant use [NOT] IN if you need a match on two or more columns.
Joining avoids the subquery (but would produce the same unwanted results if you fail to qualify the columns with table names). Joining also has the problems that (a) if you use it to simulate a NOT IN, you use a bit of logic that many people don't immediately understand (the socalled "anti semi join") - a risk for future maintenance; and (b) if you use it to simulate an IN, you can get extra rows if the match criterium is non-unique in the joined table.
My coding style is to:
* Use joins only when I actually need columns from all joined tables;
* Use EXISTS and NOT EXISTS for any check of existence in a different table (or view) source;
* Use IN and NOT IN only with a list of constants, never with a subquery (because that can be done with IN).
And to avoid the problem highlighted in this QotD, I religiously follow these two additional guidelines:
* For any query involving two or more tables, I assign an alias to each table (not really required, but makes the next guideline easier); and
* For any query involving two or more tables, I prefix every column with the proper table alias.
This not only prevents unexpected results when misspelling a column name, it also makes my queries easier to understand and maintain for whoever inherits them when I leave.
August 7, 2013 at 2:36 am
This was removed by the editor as SPAM
August 7, 2013 at 3:14 am
Good explanation Kingston 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 3:28 am
Interesting question, but the explanation could be a bit better.
Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 8:08 am
Hugo Kornelis (8/7/2013)
My coding style is to:
* Use joins only when I actually need columns from all joined tables;
* Use EXISTS and NOT EXISTS for any check of existence in a different table (or view) source;
* Use IN and NOT IN only with a list of constants, never with a subquery (because that can be done with IN).
And to avoid the problem highlighted in this QotD, I religiously follow these two additional guidelines:
* For any query involving two or more tables, I assign an alias to each table (not really required, but makes the next guideline easier); and
* For any query involving two or more tables, I prefix every column with the proper table alias.
This not only prevents unexpected results when misspelling a column name, it also makes my queries easier to understand and maintain for whoever inherits them when I leave.
+1 This... I think Hugo and I follow the same basic practices here...
tell me Hugo, do you put commas at the end or beginning of a line?
August 7, 2013 at 9:36 am
Hi all, I'm new to this site, but is there the assumption that the question is only asked of a minimum version of SQL Server?
SQL Server 2005 doesn't allow comma-separated inserts for example, so the code in question does throw an error unless it's changed to:
Insert into @customer Values (1,'JOHNNY BRAVO')
Insert into @customer Values (2,'MICKI')
Insert into @customer Values (3,'POWERPUFF GIRLS')
etc.
I know I'm nitpicking considering it's an 8 year old product, but some of us still use it 😉
August 7, 2013 at 9:37 am
Good question. I missed this because on SQL 2008 R2 I get error with 'c.ID could not be bound'. So I picked SQL would return an error. Obviously I missed the whole point of the question. But I have noticed a lot of times I get the wrong answer because of my older SQL version.
August 7, 2013 at 9:39 am
Ah, posted my answer before I read roe3p post. Same idea I was trying to make.
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply