SQL Reference ยท Statements and Clauses
SQL FROM clause
Reference for the SQL FROM clause: name tables, views and subqueries, join sources, use aliases, and see how FROM differs across SQLite, MySQL and Oracle.
The FROM clause says where a query gets its rows. It can name a table or a view, join several of them together, or wrap a subquery that acts like a temporary table. Logically it is the first clause evaluated: everything else (WHERE, GROUP BY, the select list) works on the rows FROM produces.
SELECT p.name, c.name AS category FROM products AS p INNER JOIN categories AS c ON c.id = p.category_id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
FROM table_reference [, table_reference ...] table_reference: table_name [ [AS] alias ] | ( subquery ) [AS] alias | table_reference join_type JOIN table_reference ON condition | table_reference join_type JOIN table_reference USING (column [, ...])
| Part | Description |
|---|---|
table_name |
A table or view. May be schema-qualified (schema.table) in dialects that have schemas. |
alias |
A short name for the source. Once you give an alias, use it instead of the original name. |
( subquery ) |
A derived table. Most dialects require an alias for it. |
JOIN ... ON |
Combines two sources. See INNER JOIN and LEFT JOIN. |
| Comma list | FROM a, b is an old spelling of a CROSS JOIN b. Prefer explicit JOIN. |
Returns: the combined rows that later clauses filter, group and project.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
Query without FROM |
Yes | Yes | Yes | Yes | No before 23ai: FROM dual |
| Alias for a subquery | Optional | Required | Required | Required | Optional |
AS before table alias |
Optional | Optional | Optional | Optional | Not allowed |
| Schema-qualified names | main.t for attached databases |
schema.t |
db.t |
db.schema.t |
schema.t |
-- Oracle: no AS for table aliases SELECT p.name FROM products p;
Usage notes
- Use explicit joins.
FROM a, b WHERE a.id = b.a_idworks, but forgetting theWHEREsilently produces every combination of rows. - Aliases hide the original name. After
FROM products AS p, writingproducts.nameis an error in most dialects. - A derived table is evaluated as a unit. Its columns can be filtered or joined like any table; see derived tables.
- Views and CTEs can appear wherever a table can. Use
WITHto name a subquery once and reuse it. - Table-valued functions and
VALUEScan also be sources, depending on the dialect. - Self-joins need two aliases for the same table; see self join.
Related
SELECT,WHERE,AS,INNER JOIN,WITH- Lessons: SELECT in depth, Aliases, Joins