A Common Query Error
So Many Choices
When you only need stuff from a table where there’s matching (or not!) data in another table, the humble query writer has many choices.
- Joins
- In/Not In
- Exists/Not Exists
No, this isn’t about how NOT IN breaks down in the presence of NULLs, nor is it a performance comparison of the possibilities.
Wrong Turn At Albuquerque
This is a more fundamental problem that I see people running into quite often: dealing with duplicates.
Take this query, and the results…
|
1 2 3 4 5 6 |
SELECT TOP 100 u.DisplayName FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId ORDER BY u.Id; |
It produces a number of duplicates!
This is what we’d want if we were getting any data from the Posts table, aggregating it, or if we needed it to join off somewhere else.
But we’re not, and now we’re going to make a very common mistake: We’re doing to change the wrong part of our query.
A Million
A touch of distinct…
|
1 2 3 4 5 6 |
SELECT DISTINCT TOP 100 u.DisplayName FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId ORDER BY u.Id; |
Oh, but..
|
1 2 |
Msg 145, Level 15, State 1, Line 18 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. |
Maybe though…
|
1 2 3 4 5 6 7 |
SELECT TOP 100 u.DisplayName FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId GROUP BY u.DisplayName ORDER BY u.Id; |
|
1 2 |
Msg 8127, Level 16, State 1, Line 31 Column "dbo.Users.Id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. |
Yeah nah.
This is when I start to see all sorts of creative stuff, like ordering by MIN or MAX, wild subqueries, temp tables, dynamic SQL.
Calm down.
Yelling Geronimo
Maybe a join isn’t what you’re after. Maybe you need something else.
We got you covered.
|
1 2 3 4 5 6 7 |
SELECT TOP 100 u.DisplayName FROM dbo.Users AS u WHERE EXISTS ( SELECT 1 / 0 FROM dbo.Posts AS p WHERE p.OwnerUserId = u.Id ) ORDER BY u.Id; |
This results in an already distinct list of Display Names that can be ordered without trial or tribulation.
A subquery would also work here.
Style Guide
I don’t have too many rules for how queries should be written, but I do have this one:
I use EXISTS or NOT EXISTS if I’m referencing a subquery, and IN/NOT IN when I have a list of literal values.
Thanks for reading!
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
This works…why does Select 1/0 work in the Exists, but not outside of it?
You can put anything you want in the select list of exists or not exists. It doesn’t get selected.
Thanks!
“Brace yourself: someone on the Internet said something. It’s probably wrong, too.”
Hey Erik,
That ain’t quite true, dear SELECT Bob, according to Kenneth Fisher’s article: 🙂
There EXISTS a place where SELECT 1/0 doesn’t return an error.
https://sqlstudies.com/2017/09/07/there-exists-a-place-where-select-10-doesnt-return-an-error/
Tom – yeah, I do that in a lot of places. It’s even in the Blitz scripts. I’m not sure what you’re getting at.
Thanks!
Hi Erik,
Hi Erik,
Your first reply to Kevin omits the word “nearly”.
If you can truly put anything, try using SELECT Bob. Kenneth Fisher provides this example in his article.
Now, to be fair, you did say “nearly anything” in your later reply, in this thread, to Tim.
Help, I don’t understand SELECT 1/0
First saw this at a PASS Summit, blew my mind that it didn’t actually perform the subquery, but instead checks only to see if one or more rows WOULD be returned. What I’m wondering is, if stats are not up to snuff, would this potentially not work as expected?
I also follow your same rule Eric on EXISTS vs IN. Your use of 1/0 in the sub query was awesome!
Is there any benefit to doing the select 1/0 versus select 1 in the subquery? The exists will bail on the first row found so both should produce the same plan, correct? Just trying to grok if this is a technique I should add to my toolbox.
Tim — no, like I mentioned, you can put nearly anything in there and it won’t matter.
I like to use 1/0 to help remind others who read my code that nothing is being selected, since I work with some who aren’t fully on board with using EXISTS with a subquery as a predicate yet.
Isn’t the query that is utilizing the EXISTS operator sorting by a different column value than what all the previous queries are attempting to sort by, and is therefore not truly achieving what the desired results of the original query attempts may have been?
Do you want to show user DisplayNames of only users that have posted, sorted by their 1st Post Id? Or do you want to show user DisplayNames of only users that have posted, sorted by their User Id?
I had the same thought.
Thanks for catching that! Just a copy/paste mishap.
I use [NOT] EXISTS liberally. Because the select isn’t evaluated (hence the 1/0 working), I like to put a note there. Something like:
SELECT *
FROM tab1 t1
WHERE EXISTS (SELECT 'Check that SomeFlag is set'
FROM tab2 t2
WHERE t1.ID = t2.ID
and t2.SomeFlag = 1)
or you can use a tier breaker in the ORDER BY clause, as it usually is the correct way
This is definitely a very underutilized technique. I tend to forget about it myself. Thanks for the reminder!