# SQL FROM Clause: Specify Tables and Data Sources

- URL: https://betteranalyst.com/sql/from

SQL guide · Querying

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

- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL AS (alias)](https://betteranalyst.com/sql/as)
- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL UNION](https://betteranalyst.com/sql/union)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)