FROM

The FROM clause specifies the sources for data for a query. A source can be a table, table function, subquery, VALUES, or a combination of any of those through joins.

Tables

A single table may be referenced in the FROM clause, which will only produce columns from that table.

Select from a table named cities:

SELECT * FROM cities;

Table references may also be qualified with a schema and possibly database name.

Select from a table named cities in a schema named census:

SELECT * FROM census.cities;

Table Functions

Table functions are functions that produce tabular data, and can be used in the FROM clause.

Select from a series of integers using the table function generate_series:

SELECT * FROM generate_series(1, 10);

Subqueries

Subqueries are nested queries that produce one or more columns.

Select from a subquery that's selecting from a table named cities:

SELECT *
FROM (SELECT name, state FROM cities);

Values

VALUES can be used to provide literal row values:

Produce two rows, with row one containing ('cat', 4) and row two containing ('dog', 5):

SELECT * FROM (VALUES ('cat', 4), ('dog', 5));

Joins

Joins produce new tables by horizontally combining columns from multiple input tables.

Cross Joins

A cross join is the simplest type of join, and produces all possible pairs between the left and right tables.

Cross join two tables:

SELECT *
FROM cities CROSS JOIN states;

The CROSS JOIN keyword can be omitted, and replaced with just a comma:

SELECT *
FROM cities, states;

Conditional Joins

Conditional joins are joins that specify the predicate used when joining rows between tables.

Join states onto cities to get information about the state's capital:

SELECT *
FROM states JOIN cities
ON states.abbr = cities.state_abbr

This will produce rows like:

A subset of the columns can be selected if not all columns are needed:

SELECT states.*, cities.population
FROM states JOIN cities
ON states.abbr = cities.state_abbr

Producing rows with redundant data removed:

Inner/Outer joins

Inner joins are joins that will return rows only if the join predicate matches for that row. Inner joins can be specified using either INNER JOIN or just JOIN.

Outer joins are joins that return rows that both match and don't match the join predicate.

• LEFT OUTER JOIN: Return all matched rows, and all non-matched rows from the left table.
• RIGHT OUTER JOIN: Return all matched rows, and all non-matched rows from the right table.
• FULL OUTER JOIN: Return all matched rows, and all non-matched rows from the left and right table.

Lateral Joins

A lateral join allows a subquery in the FROM clause to reference columns from previous tables in the same FROM clause.

Lateral joins allow subqueries and table functions to depend on each row of the table before it.

A simple example:

SELECT *
FROM generate_series(1, 3) g(a), LATERAL (SELECT (a + 2)) s(b);

The LATERAL keyword may be omitted:

SELECT *
FROM generate_series(1, 3) g(a), (SELECT (a + 2)) s(b);