Where Clauses and Empty Tables

Last Updated 8 years ago

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.

IF OBJECT_ID('tempdb..#aggy') IS NOT NULL
DROP TABLE #aggy;

WITH x1 AS (
SELECT TOP (100)
ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS ID
FROM sys.[messages] AS [m], sys.[messages] AS [m2])
SELECT ID, 
    DATEADD(DAY, [x1].[ID] * -1, CAST(GETDATE() AS DATE ) ) [CreateDate],
    'C:\temp\' + CAST(HASHBYTES('MD5', NCHAR([x1].[ID])) AS VARCHAR(32)) + '.gif' [Path]
INTO #aggy
FROM [x1];

IF OBJECT_ID('tempdb..#usersessioninfo') IS NOT NULL
DROP TABLE #usersessioninfo;
CREATE TABLE #usersessioninfo 
(LastActionID INT IDENTITY(1,1), UserID INT, UserMessage VARCHAR(100), MessageDetails VARCHAR(100))

IF OBJECT_ID('tempdb..#aggy') IS NOT NULL

DROP TABLE #aggy;

WITH x1 AS (

SELECT TOP (100)

ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS ID

FROM sys.[messages] AS [m], sys.[messages] AS [m2])

SELECT ID,

DATEADD(DAY, [x1].[ID] * -1, CAST(GETDATE() AS DATE ) ) [CreateDate],

'C:\temp\' + CAST(HASHBYTES('MD5', NCHAR([x1].[ID])) AS VARCHAR(32)) + '.gif' [Path]

INTO #aggy

FROM [x1];

IF OBJECT_ID('tempdb..#usersessioninfo') IS NOT NULL

DROP TABLE #usersessioninfo;

CREATE TABLE #usersessioninfo

(LastActionID INT IDENTITY(1,1), UserID INT, UserMessage VARCHAR(100), MessageDetails VARCHAR(100))

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

INSERT [#usersessioninfo]
( [UserID] , [UserMessage] , [MessageDetails] )
SELECT 
@@SPID AS [UserID],
'Welcome to your folder!' AS [UserMessage],
'You have stored #' +
CAST(COUNT(*) AS VARCHAR(100)) +
' files in the last 30 days, starting on ' + 
CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) + 
' ending on ' +
CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +
'.' AS [MessageDetails]
FROM [#aggy] AS [a]
WHERE [a].[CreateDate] >= GETDATE() -30

INSERT [#usersessioninfo]

( [UserID] , [UserMessage] , [MessageDetails] )

SELECT

@@SPID AS [UserID],

'Welcome to your folder!' AS [UserMessage],

'You have stored #' +

CAST(COUNT(*) AS VARCHAR(100)) +

' files in the last 30 days, starting on ' +

CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) +

' ending on ' +

CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +

'.' AS [MessageDetails]

FROM [#aggy] AS [a]

WHERE [a].[CreateDate] >= GETDATE() -30

Everything looks great!

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.

TRUNCATE TABLE [#aggy]

INSERT [#usersessioninfo]
( [UserID] , [UserMessage] , [MessageDetails] )
SELECT 
@@SPID AS [UserID],
'Welcome to your folder!' AS [UserMessage],
'You have stored #' +
CAST(COUNT(*) AS VARCHAR(100)) +
' files in the last 30 days, starting on ' + 
CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) + 
' ending on ' +
CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +
'.' AS [MessageDetails]
FROM [#aggy] AS [a]
WHERE [a].[CreateDate] >= GETDATE() -30

TRUNCATE TABLE [#aggy]

INSERT [#usersessioninfo]

( [UserID] , [UserMessage] , [MessageDetails] )

SELECT

@@SPID AS [UserID],

'Welcome to your folder!' AS [UserMessage],

'You have stored #' +

CAST(COUNT(*) AS VARCHAR(100)) +

' files in the last 30 days, starting on ' +

CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) +

' ending on ' +

CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +

'.' AS [MessageDetails]

FROM [#aggy] AS [a]

WHERE [a].[CreateDate] >= GETDATE() -30

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.

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.

INSERT [#usersessioninfo]
( [UserID] , [UserMessage] , [MessageDetails] )
SELECT 
@@SPID AS [UserID],
'Welcome to your folder!',
'You have # ' +
CAST(COUNT(*) AS VARCHAR(100)) +
' files, starting on ' + 
CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) + 
' ending on ' +
CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +
' in the last 30 days.'
FROM [#aggy] AS [a]
WHERE [a].[CreateDate] >= GETDATE() -30
HAVING COUNT(*) > 0

INSERT [#usersessioninfo]

( [UserID] , [UserMessage] , [MessageDetails] )

SELECT

@@SPID AS [UserID],

'Welcome to your folder!',

'You have # ' +

CAST(COUNT(*) AS VARCHAR(100)) +

' files, starting on ' +

CAST(MIN([a].[CreateDate]) AS VARCHAR(20)) +

' ending on ' +

CAST(MAX([a].[CreateDate]) AS VARCHAR(20)) +

' in the last 30 days.'

FROM [#aggy] AS [a]

WHERE [a].[CreateDate] >= GETDATE() -30

HAVING COUNT(*) > 0

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!

SQL Interview Question: “Talk me through this query.”

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.

Reply
- 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.
  
  Reply
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!

Reply