SELECT and WHERE, but no FROM clause

April 12, 2011

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.TSQL2sDay150x150 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.

This Post Has 8 Comments

  1. Brad Schulz

    Excellent argument for the UNION ALL approach!  I like it!
    (And thanks for the mention).

  2. Matt Velic

    Thanks for participating, Rob! I knew that FROM was optional, but I hadn’t thought of using APPLY instead of UNPIVOT. In my defense, I’ve never had to UNPIVOT anything, but I’m happy to have found my new method for when I do!

  3. Joe Celko

    T-SQL overloads the SELECT keyuword; ANSI does not.
    The <select sttement> does require a <FROM clause>; read your ANSI/ISO Standards.  I am doing this off the top of my head.
    The <assign statement> in Standard SQL is
    SET <target row consructor> = <expression row constructor>;
    The <assign statement> in T-SQL dialect is
    [SET | SELECT] <scalar assignment> {, <scalar assignment>};
     <scalar assignment> ::= <variable> = <expression>
    Whwre SET can take only one scalar assignment. And, as you pointed out, T-SQL is a nightmare of bad proprietary syntax.

  4. Rob Farley

    Hi Joe,
    Yes, I’m most definitely restricting myself to T-SQL here. Oracle was my first RDBMS, and I was fine with following its rules regarding SET and DUAL. Having moved to the T-SQL world, I discovered that ANSI was broken in other ways. Some I liked, some I didn’t.
    As much as I would like T-SQL to be as ANSI-like as possible, I also try to welcome the differences.

  5. Paul White

    Hey Rob,
    I’m a fan of UNPIVOT myself.  The following produces a nicer query plan (to my mind anyway) than the APPLY syntax:
    WITH    Src
    AS      (
           SELECT  SalesOrderID,
                   OrigShip =
                           WHEN ShipDate > DATEADD(DAY, 7, OrderDate)
                               THEN DATEADD(DAY, 7, OrderDate)
           FROM    Sales.SalesOrderHeader
    SELECT  D.SalesOrderID,
    FROM    Src
           [Date] FOR DateType IN (OrderDate, ShipDate, DueDate, OrigShip)
           ) AS D
    What do you think?

  6. Rob Farley

    Hi Paul,
    I’m not convinced it is nicer.
    It’s almost identical (but has an extra operator) to the version that uses VALUES. But when you compare it to the UNION ALL version, you see a few differences.
    The difference that I see as significant is that the Filter for OrigShip appears before the concatenation, but when using UNPIVOT, it puts the Filter at the end (with 125860 being checked, 94404 going through), instead of a start-up filter that would be being used only 31465 times.
    But my main point is regarding the flexibility. UNPIVOT feels so inflexible, whereas APPLY can be used in far more ways.
    Thanks for your comment though – I always appreciate your feedback on these things.

  7. RK

    I am combining two tables using a UNION operator. However, I would like to choose a subset of the resulting data with a where clause. I am getting an error when I use the where clause. Is there a simple solution to this problem.

  8. Rob Farley

       SELECT *
       FROM (
           SELECT … FROM ….
           SELECT … FROM ….
       ) AS u
       WHERE …

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs