THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

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.

image

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:

SELECT 1;

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:

SELECT @i = 1;

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

SELECT 1
WHERE @filterout = 0;

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.

SELECT -1 AS Value, '<ALL Products>' AS Label
WHERE @AllOptionAvailable = 1
UNION ALL
SELECT ProductID, Name
FROM Production.Product
ORDER BY Label;

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

SELECT @i = 1
WHERE @filterout = 0;

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:

IF (@filterout = 0) SELECT @i = 1;

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.

SELECT (SELECT Weight WHERE WeightUnitMeasureCode = 'LB'), *
FROM Production.Product;

SELECT CASE WHEN WeightUnitMeasureCode = 'LB' THEN Weight END, *
FROM Production.Product;

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:

SELECT o.SalesOrderID, d.*
FROM Sales.SalesOrderHeader o
OUTER APPLY (VALUES ('Order', o.OrderDate)
                   ,('Ship', o.ShipDate)
                   ,('Due', o.DueDate)) AS d ([DateType], [Date]);

instead of the trickier to remember:

SELECT *
FROM (SELECT SalesOrderID, OrderDate, ShipDate, DueDate FROM Sales.SalesOrderHeader) AS o
UNPIVOT ([Date] for [DateType] in (OrderDate,ShipDate,DueDate)) AS d

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

SELECT o.SalesOrderID, d.*
FROM Sales.SalesOrderHeader o
OUTER APPLY (SELECT 'Order', o.OrderDate
   UNION ALL SELECT 'Ship', o.ShipDate
   UNION ALL SELECT 'Due', o.DueDate) AS d ([DateType], [Date]);

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:

SELECT o.SalesOrderID, d.*
FROM Sales.SalesOrderHeader o
OUTER APPLY ( SELECT 'Order', o.OrderDate
    UNION ALL SELECT 'Ship', o.ShipDate
    UNION ALL SELECT 'OrigShip', DATEADD(day,7,o.OrderDate) WHERE o.ShipDate > DATEADD(day,7,o.OrderDate)
    UNION ALL SELECT 'Due', o.DueDate) AS d ([DateType], [Date]);

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.

Published Tuesday, April 12, 2011 12:05 PM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Brad Schulz said:

Excellent argument for the UNION ALL approach!  I like it!

(And thanks for the mention).

--Brad

April 11, 2011 11:31 PM
 

Matt Velic said:

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!

April 12, 2011 10:34 AM
 

Joe Celko said:

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.

April 17, 2011 11:16 AM
 

Rob Farley said:

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.

Rob

April 17, 2011 5:40 PM
 

Paul White said:

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,

               OrderDate,

               ShipDate,

               DueDate,

               OrigShip =

                   CASE

                       WHEN ShipDate > DATEADD(DAY, 7, OrderDate)

                           THEN DATEADD(DAY, 7, OrderDate)

                   END

       FROM    Sales.SalesOrderHeader

       )

SELECT  D.SalesOrderID,

       D.DateType,

       D.[Date]

FROM    Src

UNPIVOT (

       [Date] FOR DateType IN (OrderDate, ShipDate, DueDate, OrigShip)

       ) AS D

;

What do you think?

Paul

April 17, 2011 10:04 PM
 

Rob Farley said:

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.

Rob

April 17, 2011 11:33 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement