SQL Dialects Reference/Select queries/Subqueries

From Wikibooks, open books for an open world
Jump to navigation Jump to search

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 ?
MSSQL
  • SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions
    • Subquery alias is mandatory
  • WITH subquery_alias AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM subquery_alias WHERE conditions
MySQL ?
Oracle SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) WHERE conditions
PostgreSQL ?
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