SELECT Without FROM

A SELECT with no FROM evaluates its select list once and returns 1 row. There is no table to scan, so every column is a literal, an expression over literals, a function of the environment such as CURRENT_DATE, or a scalar subquery. PostgreSQL, Trino, BigQuery, SQL Server, DuckDB and current MySQL all accept it. Oracle before 23ai requires FROM DUAL, a one-row dummy table that exists for this purpose and nothing else.

The one-row query

SELECT
  CURRENT_DATE AS run_date,
  1200 * 12 AS annual_fee,
  ROUND(1200 * 12 * 0.0825, 2) AS tax_due,
  'team' AS plan;

The row carries today's date in the first column, then 14400, 1188.00 and team. This is the query that checks an expression before it goes into a 300-line report, that answers what day the scheduler thinks it is, and that confirms a connection is alive. SELECT 1 is the liveness probe most connection pools send, for the same reason: it runs without touching a table or taking a lock.

Rows from nowhere

Stacking SELECT statements with UNION ALL produces a table that exists only inside the query. Column names come from the first SELECT, and the later ones supply values in the same order. The query below declares the 4 plans the billing system is supposed to offer and subtracts the plans that accounts actually use, with the EXCEPT from earlier in the lesson.

WITH expected_plans AS (
  SELECT 'free' AS plan
  UNION ALL SELECT 'team'
  UNION ALL SELECT 'enterprise'
  UNION ALL SELECT 'partner'
)
SELECT plan FROM expected_plans
EXCEPT
SELECT plan FROM accounts;

The result is partner, a plan nobody has bought. The same inline rows serve as test fixtures: a unit test for a transformation feeds it a 4-row CTE instead of loading a table, and the expected output is another inline set, compared with EXCEPT in both directions. The VALUES form, SELECT * FROM (VALUES ('free', 0), ('team', 20)) AS t(plan, price), is shorter for the same job and is supported by PostgreSQL, Trino and SQL Server.

Scalars and sequences

SELECT
  (SELECT COUNT(*) FROM accounts) AS accounts,
  (SELECT COUNT(*) FROM all_signups) AS signups,
  (SELECT MAX(seats) FROM accounts) AS largest_account;

A scalar subquery is a complete query folded into one cell, so a FROM-less SELECT can hold several of them and return one row of figures from several tables, with no join and no fan-out. The pattern shows up in monitoring dashboards and in the first line of a backfill script that records what the tables looked like before the run.

A monitoring query must return exactly 1 row holding the row counts of accounts (8 rows), all_signups (5) and user_sessions (4). Which shape does that?

• A FROM-less SELECT with 3 scalar subqueries, one COUNT(*) per table.
• SELECT COUNT(*) FROM accounts, all_signups, user_sessions
• Three SELECT COUNT(*) queries combined with UNION ALL.
• SELECT COUNT(*) FROM accounts JOIN all_signups ON TRUE

SELECT d AS day
FROM UNNEST(SEQUENCE(DATE '2024-03-01', DATE '2024-03-07')) AS t(d);

Generating a date spine is the other side of the previous section: SEQUENCE builds an array of 7 dates and UNNEST turns it into 7 rows, with no calendar table in the database. A LEFT JOIN from that spine to daily revenue is how a report shows a zero for the day with no sales, which GROUP BY on the sales table alone would skip. PostgreSQL has generate_series for the same job and BigQuery has GENERATE_DATE_ARRAY, each feeding its own UNNEST.

Do

• Alias the outer table and qualify every column inside a correlated subquery.
• Check the plan for a kept nested loop before shipping a correlated subquery on a large table.
• Run EXCEPT in both directions when reconciling 2 tables, so lost and invented rows both show.
• Aggregate account-level numbers before UNNEST, or on DISTINCT keys after it.
• Parenthesise any chain that mixes INTERSECT with UNION or EXCEPT.

Avoid

• Don't reach for EXCEPT to deduplicate one table; that is a job for DISTINCT or a window.
• Don't sum a parent column across unnested rows; it multiplies by each row's array length.
• Don't use CROSS JOIN UNNEST when every account must appear; empty arrays vanish from the result.
• Don't assume INTERSECT ALL and EXCEPT ALL exist on every engine; the ROW_NUMBER rewrite is the fallback.

About this lesson

This section is part of the Query Structure: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.