SELECT and WHERE, but no FROM clause

re: SELECT and WHERE, but no FROM clause

Brad Schulz — Tue, 12 Apr 2011 03:31:48 GMT

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

(And thanks for the mention).

--Brad

re: SELECT and WHERE, but no FROM clause

Matt Velic — Tue, 12 Apr 2011 14:34:52 GMT

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!

re: SELECT and WHERE, but no FROM clause

Joe Celko — Sun, 17 Apr 2011 15:16:19 GMT

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>};

Whwre SET can take only one scalar assignment. And, as you pointed out, T-SQL is a nightmare of bad proprietary syntax.

re: SELECT and WHERE, but no FROM clause

Rob Farley — Sun, 17 Apr 2011 21:40:00 GMT

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

re: SELECT and WHERE, but no FROM clause

Paul White — Mon, 18 Apr 2011 02:04:42 GMT

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

re: SELECT and WHERE, but no FROM clause

Rob Farley — Mon, 18 Apr 2011 03:33:48 GMT

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