> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dynamosql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# FROM and JOINs

> Table references, aliases, subqueries in FROM, and all join types.

## Single Table with Alias

```sql theme={null}
SELECT o.id, o.total FROM myschema.orders AS o
```

Quoted table names and aliases are supported when DynamoDB identifiers include special characters or exact-case names:

```sql theme={null}
SELECT "o"."order-id", "o"."Safety.Warning"
FROM east."prod-orders" AS "o"
```

## Multiple Tables (Implicit Cross Join)

Comma-separated tables produce a cartesian product. Use a `WHERE` predicate to filter to matching rows:

```sql theme={null}
SELECT o.id, c.name
FROM myschema.orders AS o, myschema.customers AS c
WHERE o.customer_id = c.id
```

## Derived Table (Subquery in FROM)

A subquery in the `FROM` clause must have an alias:

```sql theme={null}
SELECT sub.status, COUNT(*) AS cnt
FROM (
  SELECT status FROM myschema.orders WHERE total > 100
) AS sub
GROUP BY sub.status
```

## CTE References

Tables defined in a `WITH` clause can be referenced in `FROM` like any other table:

```sql theme={null}
WITH big_orders AS (
  SELECT * FROM myschema.orders WHERE total > 500
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id
```

See [Subqueries and CTEs](/sql-reference/subqueries-and-ctes) for full CTE syntax.

## VALUES Constructor

Use `VALUES` to produce an inline table:

```sql theme={null}
SELECT v.status_code, v.label
FROM (VALUES ('pending', 'Awaiting'), ('shipped', 'On the way')) AS v(status_code, label)
```

## INNER JOIN

Join on an equality or arbitrary condition:

```sql theme={null}
-- ON syntax
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id

-- USING syntax (columns must share the same name)
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c USING (customer_id)

-- USING also accepts quoted identifiers
SELECT l.value, r.value
FROM left_data AS l
INNER JOIN right_data AS r USING ("order-id")
```

## Outer Joins

```sql theme={null}
-- LEFT OUTER JOIN: all rows from the left table; NULL for unmatched right columns
SELECT c.name, o.id
FROM myschema.customers AS c
LEFT OUTER JOIN myschema.orders AS o ON c.id = o.customer_id

-- RIGHT OUTER JOIN
SELECT c.name, o.id
FROM myschema.orders AS o
RIGHT OUTER JOIN myschema.customers AS c ON o.customer_id = c.id

-- FULL OUTER JOIN
SELECT c.name, o.id
FROM myschema.customers AS c
FULL OUTER JOIN myschema.orders AS o ON c.id = o.customer_id
```

## Join Chains (3+ Tables)

```sql theme={null}
SELECT o.id, c.name, p.title
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
INNER JOIN myschema.products AS p ON o.product_id = p.id
```

## Non-Equi Join Predicates

Join conditions are not limited to equality:

```sql theme={null}
SELECT e.name, s.tier
FROM myschema.employees AS e
INNER JOIN myschema.salary_bands AS s ON e.salary >= s.min_salary AND e.salary < s.max_salary
```

## Join with Derived Table

```sql theme={null}
SELECT c.name, recent.total
FROM myschema.customers AS c
INNER JOIN (
  SELECT customer_id, MAX(total) AS total
  FROM myschema.orders
  GROUP BY customer_id
) AS recent ON c.id = recent.customer_id
```

## Not Supported

* `CROSS JOIN` keyword syntax (use comma syntax instead)
* `NATURAL JOIN`
* `LATERAL` table references