SELECT and WHERE, but no FROM clause

SQL Books Online is wrong (or at least, on that page).

I say that because it lists the FROM clause without brackets, suggesting that it’s not optional. This post is going to look at a few situations where we leave out the FROM clause, showing that it’s really not something to be put off by.

There are other things I don’t like about this page – the lack of a semi-colon, for example, and the fact that it seems to suggest that the order_list should be specified before ASC or DESC (when ASC or DESC would apply to each member of the list).

But the thing that I want to look at is that the FROM clause should be an optional extra. (Also, because one of my examples is using the APPLY operator, it’s going to be part of this month’s T-SQL Tuesday, hosted by Matt Velic. Go look at that page for a bunch of other posts written today about APPLY.)

In its most simple form, the SELECT statement is simply that – a SELECT statement. Like:

Of course, we can fetch data out of a table if we want, but this certainly isn’t required – we may want to perform some simple calculation or something and just see the result. This is often considered a different kind of statement – not a real SELECT statement, but I’d like to argue otherwise.

A scenario we might recognise more freely could be for assigning something to a variable, for example:

A SELECT statement with no FROM clause will return a single row of data (making it a nice option for variables). Rows can be added using UNION ALL (but I consider this the joining of two SELECT statements), or filtered out using a WHERE clause (as in the following example, for which I’m assuming we have a variable called @filterout).

I’m sure you use this pattern already when preparing data for drop-down controls, such as for SSRS parameters*. Here you use a WHERE without a FROM, and it seems perfectly normal.

*Personally, I’d rather use an SSRS Filter to indicate whether that value should be included or not, instead using a query parameter. A filter would mean that the dataset doesn’t rely on the parameter, and can fetch the dataset without waiting for the @AllOptionAvailable value.

Let’s think about the variable assignment situation with a filter.

This is an odd one. If no rows are returned by the statement, no assignment is done. It doesn’t get set to NULL, it simply gets left as it was. It’s equivalent to:

And because this second one is clearer, I’m sure most of you would use that instead.

However, one scenario that I come across relatively often where using SELECT and WHERE without FROM is in sub-queries. A sub-query can access values from the context in which it runs, accessing column data in much in the same way as a normal query can access variables that are in scope. As far as the sub-query is concerned though, those values are constants, and treated that way.

A scalar sub-query within a SELECT clause, the sub-query can return only one row (and a single value in that row). If it doesn’t return that value, NULL is used instead. However, query writers are more likely to use a CASE expression instead of a sub-query that doesn’t have a WHERE clause. The second of these is both more natural, and will also (generally) perform slightly better.

However, not all sub-queries return a single value. This is where the APPLY operator comes in, in its two forms, CROSS APPLY and OUTER APPLY. OUTER APPLY leaves rows in even if they match nothing in the APPLY sub-query, much like an OUTER JOIN, but my examples here will work the same way whichever you use.

I’m sure you’re aware of the usefulness of APPLY when unpivotting. Brad Schulz has a post about it, and if you haven’t read this, I recommend you spend time looking through it.

Essentially, he demonstrates that you can replace the UNPIVOT operator very easily using APPLY:

instead of the trickier to remember:

(I use the ‘o’ sub-query here, because UNPIVOT doesn’t give me control over which columns are returned. I rarely use UNPIVOT myself, having been long-since converted to using APPLY).

However, I quite typically don’t use Brad’s method of VALUES. I prefer the SELECT … UNION ALL SELECT … method. It comes down to the flexibility I have from SELECT statements.

A SELECT statement lets me use WHERE, which means I can apply a lot more control over which rows project in which ways.

For example I might find myself wanting to add an extra row for Orders which shipped more than seven days after the order. I can do this very easily using the UNION ALL method:

Using a WHERE filter, I can easily make sure that this row gets inserted only when required, rather than applying the filter to all those rows that come out of the APPLY operation, but again, I have a WHERE clause without a FROM.

APPLY is definitely very useful, and I love it for unpivotting. Just this week I’ve used this same method for expanding unpivotting some rows, but only the ones that need it. Using APPLY with SELECT gives me the flexibility I need – so long as I’m happy to abandon some of those conventions such as SELECT statements needing FROM.

…and just for the record, this other page lists everything correctly.