How To Return A Dummy Row Instead Of Empty Results In SQL Server
Thanks for watching!
Video Summary
In this video, I discuss a common scenario where you might want to return a row even when your query returns no results. This is particularly useful in analysis scripts or debugging sessions where it’s important to know that the search criteria didn’t match any records. To demonstrate, I show two different methods: one involving a clever use of `sys.databases` and a union operation, and another using an outer join with a derived table. Both approaches are effective and have minimal performance impact, making them valuable tools in your SQL scripting arsenal. Whether you’re a seasoned DBA or just starting out, understanding these techniques can help improve the clarity and usefulness of your queries.
Full Transcript
Erik Darling here with Darling Data. I’m cool. In today’s video, we’re going to talk about how you can return a row when you actually find empty results. This is something that I use a lot in my analysis scripts because sometimes you run a query to try to find something and it doesn’t matter if you’re debugging or if you’re returning results. Sometimes people want to know that they didn’t find a row. rows for something, right? It’s a good thing to know that like, oh yeah, we checked that but we didn’t find anything. It’s a reasonable thing for some people to want to do. So, we’re going to, I’m going to show you two different ways to do that today. If you like my channel, you can join the like 25 other people who have memberships and give me like four bucks a month, sometimes more. There are some very generous people out there in the world and I thank you kindly for your generosity. If you’d like to join their ranks and also be thanked kindly, there are some very generous people out there in the world. And I thank you kindly for your generosity. If you’d like to join their ranks and also be thanked kindly, there’s a video, there’s a link in the video description that says like become a member and you can become a member. If you cannot part with four dollars a month for whatever reason that you are keeping secret from me, we shouldn’t keep secrets, you know, because we’re in love. But if you just can’t, you know, if you’re just being like, there’s like some financial infidelity between us, you can like, you can comment and you can join the over 5,000 other data darlings out there in the world who have subscribed to the channel and who get, hit on the head with a very small hammer every time I publish a video. If you need help with SQL Server, I am the finest SQL Server consultant known on Earth. There might be better ones elsewhere in the world or other, maybe like in the multiverse or something. On Earth, I just haven’t found one yet. So you can hire me to do this stuff or anything else. And as always, my rates are reasonable.
Why didn’t that? There we go. I’m the least good power pointer on the planet. That’s why all my presentations are just demos. If you would like some very high quality, very low cost training, you can get all of mine for $150 about USD for the rest of your life by either clicking on the link in the video description or going through multiple steps out of your way to go to that URL and enter that discount code. And it can all be yours. You can have me for 24 hours. It’s a bit of an indecent proposal, but I promise I’m wearing almost the exact same Adidas attire in those videos. I have no upcoming events. I’d love to have an upcoming event. Tell me about your events. I’ll get there eventually.
All right. Let’s talk about returning rows when you don’t have one. So I guess contextually, we should go into Stack Overflow. It doesn’t matter much for this demo. But let’s say that, you know, like normally you could do something like this, which is multiple steps and kind of annoying, right? So you create a table. Well, in this case, I’m using a table variable because it doesn’t matter. Performance is not an issue here. And then, you know, we insert data into that table variable.
And if the row count from that table is greater than zero, then we select data from the table. And if it’s, if it’s zero or I guess lower, right? Then we return this thing that says the message, like the return, this message that says the table is empty. Well, you know, that’s okay, but it’s like multiple steps and you got to begin and end and mind all, mind your P’s and Q’s, buster, and all sorts of other stuff that just kind of not fun to do.
But there are other ways to do that like this. And this is a, I’m particularly fond of this one because I think, I think this one is quite clever. Where if the, we select from the thing, right? In this case, we’re selecting from sys.databases and we don’t, there’s no database ID higher than this. So we can never find anything. And then we union all that to this. But on the union all part of the query, we have this, say, where not exists select from this CTE up here.
So if nothing ends up up here, then we return results down here, which is wonderful, right? Because if we don’t find a result all in one query, we can just spit a, spit a row back out, right? Say there was nothing in there, right? So that’s, that’s my favorite way of doing it. And of course that the inverse works where if we do find rows, right? We’re just going to say where database ID is greater than one. We get all the rows back that we would care about, but not the dummy row from the bottom one, because something did exist in the CTE up there.
So that’s one way of doing it. Another way of doing it. And I have the written version of this on my blog, on my blog, in a blog post on my blog, the blog, the blog. And someone in the comments left this as a suggestion. And I think this is also clever, where you’re, you have like a derived thing, which is like kind of almost the same thing. And then you left outer join that to whatever, you know, query you care about. And if you don’t find anything in that query through the magic of is null, you can return a result that just shows you when the thing was empty.
And of course, if you run that for a query where something does exist, you get all the rows back. So there’s two, two different ways that I’ve found. Well, one that I’ve found and one that was given to me on my blog by, I forget who it’s been a while, but they gave me that, that, that suggestion. And that works pretty well, too. So if you are ever writing something and you’re like, well, I don’t want to just return an empty result set.
I want to return like a dummy row that tells people, hey, we didn’t find anything. Those are a couple of ways to do it. And they work pretty well. And there’s not really any performance impact to them. So good stuff there. All right. Cool. Well, that’s it for me in this video. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And remember, if you are a major network executive and you’re looking for a young, handsome talk show host to interview Hollywood celebrities about their lives, interests, their loves, passions, I’m available. Sooner rather than later. I won’t always be this young and good looking. Someday I’ll be old and good looking. Anyway. All right. I’ll have to, I’ll have to submit some headshots then. All right. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.