Product DocumentationAppearance
FROM Clause
Use the FROM clause to specify one or more table references, such as table names, view names, and tables derived from subqueries. Join tables in either the FROM clause or the WHERE clause. See also Subquery Examples.
FROM
table_reference [ * ] [ [ AS ] table_alias [ ( column_alias [, ...] ) ] ]
[ join_type table_reference ON join_condition | USING ( join_column [, ...] ) ]
[, ...]Parameters
- table_reference
Name a persistent or temporary table, a view, a table derived from a subquery, or a
SETOFstored procedure.Regular table references may consist of one, two, or three parts, separated by periods:
- Database name
- Schema name
- Table name
For example:
premdb.public.team.Fully qualified (three-part) table names are required for remote table references in cross-database queries. However, note that you must
SELECTfrom (orCALL) stored procedures from the database where the procedures were created.You cannot combine user-defined tables and system tables or system views in the same query. One exception to this rule is the sys.const table, which can be queried in conjunction with regular tables.
- table_alias
Give an alias to a table. The alias serves as the table name for the duration of the query.
- column_alias
Give an alias to the columns in a table. The aliases serve as the table's column names for the duration of the query.
- join_type
Use one of the following join types. The INNER and OUTER keywords are optional.
- INNER JOIN or JOIN
- FULL OUTER JOIN or FULL JOIN
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- CROSS JOIN
An inner join returns matching rows, based on a FROM clause or WHERE clause join condition.
An outer join must be specified in the FROM clause. An outer join returns the same set of rows that an inner join would return, but it also returns non-matching rows from the "left" table (left outer join), the "right" table (right outer join), or both tables (full outer join). In this context, the table that is listed first in the FROM clause is the left table, and the table listed second is the right table. The output columns that contain NULLs indicate the non-matching rows that the outer join returns.
A cross join returns the Cartesian product of the rows in both tables. It is an unqualified join without a join condition. The
CROSS JOINsyntax is equivalent to writing simplyFROM table1, table2. You can use aWHEREclause condition with a cross join if required, but theONsyntax is not supported.- ON join_condition
Express each inner or outer join in terms of a search condition, such as an equality condition:
hometeam join team on hometeam.htid=team.htidInequality conditions are supported for inner joins, left and right outer joins, and cross joins. Full outer joins with inequality conditions are not supported. For example, the following join is supported:
hometeam left outer join awayteam on hometeam.htid>awayteam.atidAn
ONjoin retains both joining columns in its intermediate result set. Parentheses are not required for the join condition but may be used for clarity.- USING (join_column)
Express an inner or outer join in terms of a list of one or more columns that exist in both tables. Parentheses are required. For example:
hometeam join team using(htid)The implicit join condition is an equality condition. A
USINGjoin retains one of the joining columns in its intermediate result set, not both.
Examples
Join two tables with USING syntax:
premdb=# select teamid, htid, atid, team.name, nickname, city, stadium, capacity
from hometeam join team using(htid)
where teamid between 20 and 29
order by teamid, htid, atid;
teamid | htid | atid | name | nickname | city | stadium | capacity
--------+------+------+-------------------+------------+---------------+--------------------+----------
20 | 21 | 70 | Ipswich Town | Blues | Ipswich | Portman Road | 30311
21 | 22 | 71 | Leeds United | Whites | Leeds | Elland Road | 39460
22 | 23 | 72 | Leicester City | Foxes | Leicester | King Power Stadium | 32262
23 | 24 | 73 | Liverpool | Reds | Liverpool | Anfield | 44742
24 | 25 | 74 | Manchester City | Citizens | Manchester | Etihad Stadium | 55097
25 | 26 | 75 | Manchester United | Red Devils | Manchester | Old Trafford | 75635
26 | 27 | 76 | Middlesbrough | Boro | Middlesbrough | Riverside Stadium | 34742
27 | 28 | 77 | Newcastle United | Magpies | Newcastle | St. James Park | 52405
28 | 29 | 78 | Norwich City | Canaries | Norwich | Carrow Road | 27244
29 | 30 | 79 | Nottingham Forest | Forest | Nottingham | City Ground | 30445
(10 rows)Here is the same query with JOIN ON syntax:
premdb=# select teamid, team.htid, atid, team.name, nickname, city, stadium, capacity
from hometeam join team on hometeam.htid=team.htid
where teamid between 20 and 29
order by teamid, htid, atid;The following query uses a full outer join to return matching and non-matching rows in the hometeam and awayteam tables:
premdb=# select ht.htid, at.atid, ht.name
from hometeam ht full outer join awayteam at on ht.name=at.name
order by ht.name nulls first;
htid | atid | name
------+------+-------------------------
| 98 |
| 99 |
| 100 |
51 | |
50 | |
49 | |
2 | 51 | Arsenal
3 | 52 | Aston Villa
4 | 53 | Barnsley
5 | 54 | Birmingham City
6 | 55 | Blackburn Rovers
7 | 56 | Blackpool
...The equivalent left outer join query returns fewer rows:
premdb=# select ht.htid, at.atid, ht.name
from hometeam ht left outer join awayteam at on ht.name=at.name
order by ht.name nulls first;
htid | atid | name
------+------+-------------------------
51 | |
50 | |
49 | |
2 | 51 | Arsenal
3 | 52 | Aston Villa
4 | 53 | Barnsley
5 | 54 | Birmingham City
6 | 55 | Blackburn Rovers
7 | 56 | Blackpool
...Return the unqualified cross-join results from two tables:
premdb=# select * from awayteam cross join hometeam;
atid | name | htid | name
------+-------------------------+------+-------------------------
51 | Arsenal | 2 | Arsenal
51 | Arsenal | 3 | Aston Villa
51 | Arsenal | 4 | Barnsley
51 | Arsenal | 5 | Birmingham City
51 | Arsenal | 6 | Blackburn Rovers
...