What can you do with a SELECT statement without a FROM clause?
4October 25, 2022 by Kenneth Fisher
Some people have shower thoughts, I have 1am thoughts. In this case it was the only keyword required in a select statement is SELECT. This is probably pretty basic to most of you but you can do simple things like this:
Display one or more pieces of information.
DECLARE @HelloWorld nvarchar(20) = 'Hi!';
SELECT @HelloWorld AS [Display A Variable];
SELECT 4-2 AS [Do Math], GetDate() AS [Run A Function];
SELECT @@Language AS [System Variables], 'Etc' AS [Literals];
SET one or more variables.
DECLARE @MyName varchar(20);
DECLARE @Counter int;
SELECT @MyName = 'Ken', @Counter = 0;
My epiphany of the night though was that you could have a SELECT statement with a WHERE clause but no FROM. I’m not entirely sure why this excited me so much. All I can do is point out that it was 1am and I am not a night person. Either way, this works.
DECLARE @Hi nvarchar(20) = 'Hi!';
SELECT @Hi AS [Good Morning]
WHERE datepart(hour,getdate()) BETWEEN 6 AND 11;
SELECT @Hi AS [Good Afternoon]
WHERE datepart(hour,getdate()) BETWEEN 12 AND 18;
So why would you want to do this? The best I can come up with is a conditional SET statement. But an IF statement and a SET statement will work just as well and probably be more understandable. I thought it might be useful to as a conditional information display, for example if you have a print variable set, but since it displays the column information regardless it wouldn’t work the way you’d want it to, and again, an IF statement outside the SELECT will work better.
I have to say that while I’m still highly amused by the idea of a SELECT/WHERE only statement, as far as I can tell it’s pretty useless. Although I’d be more than happy to be proven wrong.
SQL Studies 
I once had an interviewer ask me what the shortest select state was and I answered
SELECT ” (two single quotes)
He wanted to argue that “select 1” was the shortest. But really in retrospect “select NULL” was a better answer. {I didn’t take the job.}
Here’s a SELECT without a WHERE clause:
DECLARE @hi nvarchar(2) = ‘Hi!’;
SELECT Case WHEN DATEPART(hour,GETDATE()) between 6 and 11 THEN @hi + ‘ Good morning.’
WHEN DATEPART(hour,GETDATE()) between 12 and 18 THEN @hi + ‘ Good afternoon.’
WHEN DATEPART(hour,GETDATE()) between 19 and 23 THEN @hi + ‘ Good afternoon.’
Else @hi + ‘ Good night’
END Greeting
Very nice! And yea, I get where he was trying to go but really it sounds like it was nitpicking 🙂
Via Registered Servers, find which Instances still have Version 2016:
select @@version
where @@version like ‘Microsoft SQL Server 2016%’
I use a Server-Driven UI framework where all screen definitions and associated data retrieval are created in stored procedures. I have full screen definitions with a single SELECT statement, one column per label or field rendered on the screen, and all data retrieval handled by UDF calls. No FROM or WHERE clause, and it’s wicked fast!