Movatterモバイル変換

A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is

T1join_typeT2 [join_condition]

Joins of all types can be chained together, or nested: either or bothT1 andT2 can be joined tables. Parentheses can be used aroundJOIN clauses to control the join order. In the absence of parentheses,JOIN clauses nest left-to-right.

To put this together, assume we have tablest1:

 num | name-----+------   1 | a   2 | b   3 | c

andt2:

 num | value-----+-------   1 | xxx   3 | yyy   5 | zzz

then we get the following results for the various joins:

=>SELECT * FROM t1 CROSS JOIN t2; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   1 | a    |   3 | yyy   1 | a    |   5 | zzz   2 | b    |   1 | xxx   2 | b    |   3 | yyy   2 | b    |   5 | zzz   3 | c    |   1 | xxx   3 | c    |   3 | yyy   3 | c    |   5 | zzz(9 rows)=>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   3 | c    |   3 | yyy(2 rows)=>SELECT * FROM t1 INNER JOIN t2 USING (num); num | name | value-----+------+-------   1 | a    | xxx   3 | c    | yyy(2 rows)=>SELECT * FROM t1 NATURAL INNER JOIN t2; num | name | value-----+------+-------   1 | a    | xxx   3 | c    | yyy(2 rows)=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   2 | b    |     |   3 | c    |   3 | yyy(3 rows)=>SELECT * FROM t1 LEFT JOIN t2 USING (num); num | name | value-----+------+-------   1 | a    | xxx   2 | b    |   3 | c    | yyy(3 rows)=>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   3 | c    |   3 | yyy     |      |   5 | zzz(3 rows)=>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   2 | b    |     |   3 | c    |   3 | yyy     |      |   5 | zzz(4 rows)

The join condition specified withON can also contain conditions that do not relate directly to the join. This can prove useful for some queries but needs to be thought out carefully. For example:

=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx   2 | b    |     |   3 | c    |     |(3 rows)

Notice that placing the restriction in theWHERE clause produces a different result:

=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx'; num | name | num | value-----+------+-----+-------   1 | a    |   1 | xxx(1 row)

This is because a restriction placed in theON clause is processedbefore the join, while a restriction placed in theWHERE clause is processedafter the join. That does not matter with inner joins, but it matters a lot with outer joins.

A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query. This is called atable alias.

To create a table alias, write

FROMtable_reference ASalias

or

FROMtable_referencealias

TheAS key word is optional noise.alias can be any identifier.

A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example:

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

The alias becomes the new name of the table reference so far as the current query is concerned — it is not allowed to refer to the table by the original name elsewhere in the query. Thus, this is not valid:

SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- wrong

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.:

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

Parentheses are used to resolve ambiguities. In the following example, the first statement assigns the aliasb to the second instance ofmy_table, but the second statement assigns the alias to the result of the join:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROMtable_reference [AS]alias (column1 [,column2 [, ...]] )

If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.

When an alias is applied to the output of aJOIN clause, the alias hides the original name(s) within theJOIN. For example:

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

is valid SQL, but:

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

is not valid; the table aliasa is not visible outside the aliasc.

Subqueries specifying a derived table must be enclosed in parentheses. They may be assigned a table alias name, and optionally column alias names (as inSection 7.2.1.2). For example:

FROM (SELECT * FROM table1) AS alias_name

This example is equivalent toFROM table1 AS alias_name. More interesting cases, which cannot be reduced to a plain join, arise when the subquery involves grouping or aggregation.

A subquery can also be aVALUES list:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))     AS names(first, last)

Again, a table alias is optional. Assigning alias names to the columns of theVALUES list is optional, but is good practice. For more information seeSection 7.7.

According to the SQL standard, a table alias name must be supplied for a subquery.Postgres Pro allowsAS and the alias to be omitted, but writing one is good practice in SQL code that might be ported to another system.

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in theFROM clause of a query. Columns returned by table functions can be included inSELECT,JOIN, orWHERE clauses in the same manner as columns of a table, view, or subquery.

Table functions may also be combined using theROWS FROM syntax, with the results returned in parallel columns; the number of result rows in this case is that of the largest function result, with smaller results padded with null values to match.

function_call [WITH ORDINALITY] [[AS]table_alias [(column_alias [, ...])]]ROWS FROM(function_call [, ...] ) [WITH ORDINALITY] [[AS]table_alias [(column_alias [, ...])]]

If theWITH ORDINALITY clause is specified, an additional column of typebigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1. (This is a generalization of the SQL-standard syntax forUNNEST ... WITH ORDINALITY.) By default, the ordinal column is calledordinality, but a different column name can be assigned to it using anAS clause.

The special table functionUNNEST may be called with any number of array parameters, and it returns a corresponding number of columns, as ifUNNEST (Section 9.19) had been called on each parameter separately and combined using theROWS FROM construct.

UNNEST(array_expression [, ...] ) [WITH ORDINALITY] [[AS]table_alias [(column_alias [, ...])]]

If notable_alias is specified, the function name is used as the table name; in the case of aROWS FROM() construct, the first function's name is used.

If column aliases are not supplied, then for a function returning a base data type, the column name is also the same as the function name. For a function returning a composite type, the result columns get the names of the individual attributes of the type.

Some examples:

CREATE TABLE foo (fooid int, foosubid int, fooname text);CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$    SELECT * FROM foo WHERE fooid = $1;$$ LANGUAGE SQL;SELECT * FROM getfoo(1) AS t1;SELECT * FROM foo    WHERE foosubid IN (                        SELECT foosubid                        FROM getfoo(foo.fooid) z                        WHERE z.fooid = foo.fooid                      );CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);SELECT * FROM vw_getfoo;

In some cases it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudo-typerecord with noOUT parameters. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. This syntax looks like:

function_call [AS]alias (column_definition [, ...])function_call AS [alias] (column_definition [, ...])ROWS FROM( ...function_call AS (column_definition [, ...]) [, ...] )

When not using theROWS FROM() syntax, thecolumn_definition list replaces the column alias list that could otherwise be attached to theFROM item; the names in the column definitions serve as column aliases. When using theROWS FROM() syntax, acolumn_definition list can be attached to each member function separately; or if there is only one member function and noWITH ORDINALITY clause, acolumn_definition list can be written in place of a column alias list followingROWS FROM().

Consider this example:

SELECT *    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')      AS t1(proname name, prosrc text)    WHERE proname LIKE 'bytea%';

Thedblink function (part of thedblink module) executes a remote query. It is declared to returnrecord since it might be used for any kind of query. The actual column set must be specified in the calling query so that the parser knows, for example, what* should expand to.

This example usesROWS FROM:

SELECT *FROM ROWS FROM    (        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')            AS (a INTEGER, b TEXT),        generate_series(1, 3)    ) AS x (p, q, s)ORDER BY p;  p  |  q  | s-----+-----+---  40 | foo | 1 100 | bar | 2     |     | 3

It joins two functions into a singleFROM target.json_to_recordset() is instructed to return two columns, the firstinteger and the secondtext. The result ofgenerate_series() is used directly. TheORDER BY clause sorts the column values as integers.

Subqueries appearing inFROM can be preceded by the key wordLATERAL. This allows them to reference columns provided by precedingFROM items. (WithoutLATERAL, each subquery is evaluated independently and so cannot cross-reference any otherFROM item.)

Table functions appearing inFROM can also be preceded by the key wordLATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by precedingFROM items in any case.

ALATERAL item can appear at the top level in theFROM list, or within aJOIN tree. In the latter case it can also refer to any items that are on the left-hand side of aJOIN that it is on the right-hand side of.

When aFROM item containsLATERAL cross-references, evaluation proceeds as follows: for each row of theFROM item providing the cross-referenced column(s), or set of rows of multipleFROM items providing the columns, theLATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

A trivial example ofLATERAL is

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

This is not especially useful since it has exactly the same result as the more conventional

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function. For example, supposing thatvertices(polygon) returns the set of vertices of a polygon, we could identify close-together vertices of polygons stored in a table with:

SELECT p1.id, p2.id, v1, v2FROM polygons p1, polygons p2,     LATERAL vertices(p1.poly) v1,     LATERAL vertices(p2.poly) v2WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

This query could also be written

SELECT p1.id, p2.id, v1, v2FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

or in several other equivalent formulations. (As already mentioned, theLATERAL key word is unnecessary in this example, but we use it for clarity.)

It is often particularly handy toLEFT JOIN to aLATERAL subquery, so that source rows will appear in the result even if theLATERAL subquery produces no rows for them. For example, ifget_product_names() returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this:

SELECT m.nameFROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON trueWHERE pname IS NULL;

Note

The join condition of an inner join can be written either in theWHERE clause or in theJOIN clause. For example, these table expressions are equivalent:

FROM a, b WHERE a.id = b.id AND b.val > 5

and:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

or perhaps even:

FROM a NATURAL JOIN b WHERE b.val > 5

Which one of these you use is mainly a matter of style. TheJOIN syntax in theFROM clause is probably not as portable to other SQL database management systems, even though it is in the SQL standard. For outer joins there is no choice: they must be done in theFROM clause. TheON orUSING clause of an outer join isnot equivalent to aWHERE condition, because it results in the addition of rows (for unmatched input rows) as well as the removal of rows in the final result.

Tip

Grouping without aggregate expressions effectively calculates the set of distinct values in a column. This can also be achieved using theDISTINCT clause (seeSection 7.3.3).

Note

The construct(a, b) is normally recognized in expressions as arow constructor. Within theGROUP BY clause, this does not apply at the top levels of expressions, and(a, b) is parsed as a list of expressions as described above. If for some reason youneed a row constructor in a grouping expression, useROW(a, b).