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…

D-D-D

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…

Oh, but..

Maybe though…

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.

This results in an already distinct list of Display Names that can be ordered without trial or tribulation.

Figuring.

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!

Previous Post
Setting Up SQL Server: People Still Need Help
Next Post
How Trace Flag 2335 Affects Memory Grants

18 Comments. Leave new

  • This works…why does Select 1/0 work in the Exists, but not outside of it?

    Reply
  • 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?

    Reply
  • Tim Cartwright
    August 14, 2018 11:12 am

    I also follow your same rule Eric on EXISTS vs IN. Your use of 1/0 in the sub query was awesome!

    Reply
    • Tim Cartwright
      August 14, 2018 11:25 am

      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.

      Reply
      • Tim — no, like I mentioned, you can put nearly anything in there and it won’t matter.

        Reply
      • Andrew Kopittke
        August 14, 2018 6:35 pm

        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.

        Reply
  • Austin Harrison
    August 14, 2018 11:20 am

    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?

    Reply
  • Steven Hibble
    August 15, 2018 1:22 pm

    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)

    Reply
  • or you can use a tier breaker in the ORDER BY clause, as it usually is the correct way

    Reply
  • Roland Alexander
    August 21, 2018 6:01 am

    This is definitely a very underutilized technique. I tend to forget about it myself. Thanks for the reminder!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}