SQL Dialects Reference/Select queries/Subqueries

Subqueries in FROM clause edit

Standard Subquery (ANSI calls it <derived table>) alias is mandatory:[1]

SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions

DB2 ?
Firebird ?
Ingres ?
Linter ?
MonetDB
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
MSSQL
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
MySQL ?
Oracle SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) WHERE conditions
PostgreSQL
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions
SQLite
  • SELECT … FROM (SELECT …) [AS alias] WHERE …
  • WITH cte AS (SELECT …) SELECT … FROM cte WHERE …
Virtuoso ?
  1. https://jakewheat.github.io/sql-overview/sql-2011-foundation-grammar.html#table-primary