SQL guide · Querying

SQL FROM Clause: Specify Tables and Data Sources

The FROM clause tells the database where a query gets its rows. In the simplest case it names one table, but it can also name a view, a subquery (often called a derived table), a common table expression, or several of these combined with joins. Everything else in the query, including the columns you select and the conditions in WHERE, refers to what FROM makes available.

Logically, FROM is the first clause evaluated. The database builds the full set of source rows first, applying any joins, and only then filters, groups and projects columns. That is why you can use a table alias defined in FROM anywhere else in the query, while an alias defined in SELECT is not visible in WHERE.

When a query reads from more than one table, give each table a short alias and qualify column names with it, for example o.order_id. This avoids ambiguity when two tables share a column name and makes the query much easier to read. Listing several tables separated by commas creates a cross join, so modern SQL prefers explicit JOIN ... ON syntax, which keeps the join condition next to the tables it connects.

Basic syntax

SELECT columns
FROM table_name [AS] alias
[JOIN other_table [AS] other_alias ON join_condition]
[, (SELECT ...) AS derived_table];

table_name can be a table, view or common table expression. A parenthesised subquery in FROM must usually have an alias. The optional AS keyword before an alias is accepted by every major database except Oracle, where it is not allowed for table aliases.

SQL FROM examples

Read from a single table

The simplest form names one table. Every row of employees is a candidate row, and the select list picks the columns to return.

SELECT employee_id, first_name, department
FROM employees;

Use a table alias

Aliases shorten the query and make column references explicit. Here c stands for customers and o for orders, and the join condition links them by customer id.

SELECT c.customer_id, c.first_name, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

Query a subquery as a derived table

A subquery in FROM behaves like a temporary table. This one first totals spend per customer, then the outer query keeps only the big spenders.

SELECT t.customer_id, t.total_spent
FROM (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS t
WHERE t.total_spent > 1000;

Read from a common table expression

A WITH clause names an intermediate result so the main query can read it like a table. This is often clearer than nesting subqueries several levels deep.

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT customer_id, COUNT(*) AS order_count
FROM recent_orders
GROUP BY customer_id;

Where SQL dialects differ

PostgreSQL: Supports derived tables, LATERAL subqueries that can reference earlier FROM items, and set-returning functions such as generate_series() directly in FROM. A derived table requires an alias before PostgreSQL 16; from version 16 the alias is optional.

MySQL: Requires an alias on every derived table. Common table expressions work from MySQL 8.0. SELECT ... FROM DUAL is accepted for table-less queries but is not required.

SQL Server: Accepts table hints in FROM, such as WITH (NOLOCK), which should be used with care. Tables can be qualified as database.schema.table. Derived tables must have an alias.

SQLite: Supports derived tables and common table expressions since version 3.8.3. A subquery in FROM does not need an alias, although adding one is good practice.

Oracle: Does not accept AS before a table alias, so write FROM employees e. A query with no real source needs FROM dual. Oracle also supports LATERAL and CROSS APPLY from version 12c.

Common mistakes

  • Listing several tables separated by commas and forgetting the join condition in WHERE. The result is a cross join with every row paired against every other row, which can be enormous.
  • Leaving column names unqualified when two tables share them. The database raises an ambiguity error, or worse, you pick the wrong column after a schema change.
  • Omitting the alias on a derived table in databases that require one, which produces a syntax error that points at the wrong place.
  • Using the original table name after assigning an alias. Once you write FROM orders o, many databases only accept o. as the qualifier.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator