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 provides consulting and training courses around the world in SQL Server and BI topics.

SELECT and WHERE, but no FROM clause

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/04/12/select-and-where-but-no-from-clause/

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

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
 

RK said:

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.

July 15, 2015 3:09 PM
 

Rob Farley said:

Try:

   SELECT *

   FROM (

       SELECT ... FROM ....

       UNION

       SELECT ... FROM ....

   ) AS u

   WHERE ...

July 15, 2015 8:15 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement