What can you do with a SELECT statement without a FROM clause?

4

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

4 thoughts on “What can you do with a SELECT statement without a FROM clause?

  1. Johan says:

    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

  2. Gert says:

    Via Registered Servers, find which Instances still have Version 2016:
    select @@version
    where @@version like ‘Microsoft SQL Server 2016%’

  3. John says:

    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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,739 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: