No table select

Every now and then everyone using SQL is facing a problem that requires some static data to be used in a query. It may be just some constant in a column or it maybe the whole dataset. First of those cases does not require any discussion. You just use a static value in a query giving it a name (it's not really required but more often then not you'll use it in a subquery):

select some_column, another_colum, 1 as constant_column
from some_table;

Obviously, it's not what this post is about. I want to talk about the case when all columns are constant. How to create such select when there is no table to query from?

It's easy. In case of SQL Server you just get rid of the from keyword (and all that's associated with it, obviously):

select 1 as x;

x
-----------
1

(1 row affected)

Things are a bit different in Oracle as Oracle have a special no-column-one-row dummy table called dual. You can query anything from this table exactly as you would do in the first example:

select 1 as x from dual;

         X
----------
         1
You can also query star (*) from it :)

select * from dual;

What you're getting is a single column, single row output:
DUMMY         
-----------
X


What are the pros and cons of each of those approaches?

For sure, Oracle enforces unified query structure where you have to use all the basic keywords of SQL which are SELECT and FROM. In my opinion the query that is stripped to just a SELECT keyword looks horrible. This is a very strong argument I'm making here ;) I'm sure, that enforcing structural integrity throughout all forms of queries made Oracle internals cleaner, easier to maintain.
It's quite common to block many of such selects into more rows with UNION ALL and then SQL Server's version is just smaller, a little bit more on the cleaner side. My personal preference though is that I like consistency more therefore - Oracle.


By the way.
I've said this couple times before - many commenters on the internet seem not to grasp the reasons why some concepts exist. They often give simple, irrelevant answers. No select queries are not used only for proving/checking how database works but those are very useful for list of values cross-or-full-outer-joined to original data to provide complete set data cells. For example - we might have some finance data and we want to group and summarize it by quarter and category. What we also know is that not every category has releveant data for each of the quarters. How would we solve it (apart from using any reporting tools ;D)?
We can cross/full outer join it with a static list, like so:
Oracle:

create table FINANCE_DATA as
select 'Q1' as quarter, 'A' as item_category, 123 as amount from dual union all
select 'Q2' as quarter, 'A' as item_category, 234 as amount from dual union all
select 'Q4' as quarter, 'B' as item_category, 456 as amount from dual union all
select 'Q1' as quarter, 'B' as item_category, 567 as amount from dual union all
select 'Q2' as quarter, 'C' as item_category, 678 as amount from dual union all
select 'Q4' as quarter, 'C' as item_category, 890 as amount from dual;


select q.quarter, nvl(f.total, q.amount) as total
from (
    select quarter, sum(amount) as total 
    from FINANCE_DATA 
    group by quarter
)f
right join
(
    select 'Q1' as quarter, 0 as amount from dual union all
    select 'Q2' as quarter, 0 as amount from dual union all
    select 'Q3' as quarter, 0 as amount from dual union all
    select 'Q4' as quarter, 0 as amount from dual
) q
on (f.quarter = q.quarter)
order by q.quarter
;

SQL Server:

select *
into FINANCE_DATA
from (
  select 'Q1' as quarter, 'A' as item_category, 123 as amount union all
  select 'Q2' as quarter, 'A' as item_category, 234 as amount union all
  select 'Q4' as quarter, 'B' as item_category, 456 as amount union all
  select 'Q1' as quarter, 'B' as item_category, 567 as amount union all
  select 'Q2' as quarter, 'C' as item_category, 678 as amount union all
  select 'Q4' as quarter, 'C' as item_category, 890 as amount 
) as f;
go



select q.quarter, isnull(f.total, q.amount) as total
from (
    select quarter, sum(amount) as total 
    from FINANCE_DATA 
    group by quarter
)f
right join
(
    select 'Q1' as quarter, 0 as amount union all
    select 'Q2' as quarter, 0 as amount union all
    select 'Q3' as quarter, 0 as amount union all
    select 'Q4' as quarter, 0 as amount 
) q
on (f.quarter = q.quarter)
order by q.quarter
;

You would be amazed how often it's used among different reporting tools.

Best