Where Clauses and Empty Tables

Sometimes SQL is the presentation layer

And when it is, you end up doing a lot of concatenating. This isn’t about performance, or trying to talk you out of SQL as the presentation layer, this is just something you should keep in mind. SQL is a confusing language when you’re just starting out. Heck, sometimes it’s even confusing when you’ve been doing it for a long time.

Let’s say your have a website that stores files, and when a user logs in you use a temp table to track session actions as a sort of audit trail, which you dump out into a larger table when they log out. Your audit only cares about folders they have files stored in, not empty ones.

Here’s a couple tables to get us going.

And then we’ll stick some data into our session table like this.

Everything looks great!

Select max blah blah blah

Select max blah blah blah

But if your table is empty…

You may find yourself with a bunch of junk you don’t care about! Empty folders. Contrived examples. Logic problems. Stay in school.

What do you think is going to happen? We truncated the table, so there’s nothing in there. Our WHERE clause should just skip everything because there are no dates to qualify.

NULLs be here!

NULLs be here!

Darn. Dang. Gosh be hecked. These are words I really say when writing SQL.

That obviously didn’t work! You’re gonna need to do something a little different.

Having having bo baving banana fana fo faving

One of the first things I was ever really proud of was using the HAVING clause to show my boss duplicate records. This was quickly diminished by him asking me to then remove duplicates based on complicated logic.

Having is also pretty cool, because it’s processed after the where clause, so any rows that make it past there will be filtered out later on down the line. For our purposes, it will keep anything from being inserted, because our COUNT is a big fat 0. Zero. Zer-roh.

This inserts 0 rows, which is what we wanted. No longer auditing empty folders! Hooray! Everybody dance drink now!

Mom will be so proud

Not only did you stay out of jail, but you wrote some SQL that worked correctly.

Thanks for reading!

Previous Post
SQL Interview Question: “Talk me through this query.”
Next Post
Creating Tables and Stored Procedures in TempDB – Permanently

3 Comments. Leave new

  • SQL Server Performance Tune
    May 3, 2016 9:25 am

    I’d rather have a message saying I have 0 files stored. That is also informative, while not knowing anything at all is … not knowing anything.

    • Erik Darling
      May 3, 2016 9:38 am

      When you write your own fake web application code to show the different behavior of WHERE and HAVING, you can display whichever message you’d like. Thanks for stopping by.

  • Joe O'Connor
    May 6, 2016 12:45 pm

    HAVING – it’s not just for use with GROUP BY – who knew? Thanks for the brain dump!

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