SQL Dialects Reference/Select queries/Subqueries

Subqueries in FROM clause

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
This article is issued from Wikibooks. The text is available under Creative Commons Attribution-Share Alike 4.0 unless otherwise noted. Additional terms may apply for the media files.