AzureFeeds

Sometimes what we’re interested in is just whether or not a row exists.

In the example shown, I’m saying I want to select product ID and description from the products table where there is at least one order line that has the picked quantity less than the ordered quantity. That means that someone ordered a certain number, but for some reason, we didn’t pick (i.e. got ready for shipment) the quantity that they wanted.

At some point we have under-supplied them. Maybe we don’t have enough stock.

SELECT 1 vs SELECT *

Notice that I’ve said SELECT 1. I could have said SELECT *. They work exactly the same. Under the covers, SELECT * takes an infinitesimally small amount of extra time because it has to be parsed, but it’s really not an issue of any type.

I prefer to write SELECT 1 as I want to make sure I’m not in any way that any of the columns that come back are relevant at all. It also helps if you have static code checking that throws warnings if you write SELECT * in a query.

EXISTS Queries

EXISTS queries are important because as soon as they find one matching rows, they know that it exists. Similarly, if you have a NOT EXISTS predicate, as soon as it finds one, it knows that the NOT EXISTS clause is not true either.

The EXISTS subquery just basically returns true or false, which is what is required for the WHERE or AND or OR predicate. Curiously, SQL Server doesn’t actually have a boolean (or true/false) data type. The ANSI SQL standard does, and I wish it did.

Common Mistake

A common mistake I see people make though, is instead of doing an EXISTS query, they put a sub query in place and they go and count the number of rows. And then they say where that sub query is greater than zero, like this:

SELECT p.ProductID, p.[Description]
FROM dbo.Products AS p
WHERE (SELECT COUNT(1) FROM dbo.OrderLines AS ol
       WHERE ol.ProductID = p.ProductID 
       AND ol.PickedQuantity 
 0;

Don’t do that.

There’s no need to count the rows if all you want to do is know if there is one row. In a perfect world, all SQL query optimizers would work out what you’re trying to say, and convert it to a more-efficient EXISTS query, but why take the chance?

Correlated Subquery

Another thing to note about this type of query is this is the first of what’s called a correlated subquery. The previous subqueries that we saw were nested ones. You could highlight them and execute them, without referencing the rest of the query.

These are not like that. They depend on the outer query. The subquery includes p.ProductID and that’s referring to a column from the main part of the query i.e., in the outer query. It means that in this case, it’s going to have to run that subquery for every single row in the products table. It’s going to have to run it time and time again. With a nested subquery, it could run the subquery once and then just use the results.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #119 Checking for Existence with EXISTS Subqueries appeared first on The Bit Bucket.