Product Documentation

Appearance

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 [ EXTERNAL ] 
table_reference [ * ] [ [ AS ] table_alias [ ( column_alias [, ...] ) ] ] 
[ join_type table_reference ON join_condition | USING ( join_column [, ...] ) ]
[, ...]

Parameters ​

EXTERNAL

Specify an external table. External table references are of the following form:

'/my_external_tables/ext_table100.csv' (c1 int, c2 char(10)) using (format text)

External table references consist of three parts:

  • Path to an external source file, relative to an NFS mount point (see CREATE EXTERNAL MOUNT)
  • Column list, naming each column and its data type
  • USING clause, naming one or more options for selecting from the file, including the format of the data

See also External Tables and CREATE EXTERNAL TABLE.

Note: You can also select from a symbolic link to a file. For example, assume you have a symbolic link like this:

~/ext_db$ ll
...
ext_team_link.csv -> /home/yb100/ext_db/ext_team.csv

A query can select from either the symbolic link, ext_team_link.csv, or the source file, ext_team.csv.

table_reference

Name a persistent or temporary table, a view, a table derived from a subquery, a SETOF stored procedure, or an external table.

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 SELECT from (or CALL) stored procedures from the database where the procedures were created.

You can combine references to regular tables and external tables in the same query.

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 JOIN syntax is equivalent to writing simply FROM table1, table2. You can use a WHERE clause condition with a cross join if required, but the ON syntax 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.htid

Inequality 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.atid

An ON join 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 USING join 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
...

The following query joins three instances of the season table: the first one is external (alias ext_s), the second one is in a remote database (alias yps), and the third is in the current database (alias pps):

premdb=# select ext_s.seasonid, ext_s.season_name, ext_s.winners from 
external 'ext_db/season.csv' 
  (seasonid int, season_name char(9), numteams int, winners varchar(40)) 
  using (format csv) 
ext_s, 
yellowbrick.public.season yps, 
premdb.public.season pps 
where ext_s.seasonid=yps.seasonid 
  and yps.seasonid=pps.seasonid;
 seasonid | season_name |      winners      
----------+-------------+-------------------
       1 | 1992-1993   | Manchester United
       2 | 1993-1994   | Manchester United
       3 | 1994-1995   | Blackburn Rovers
       4 | 1995-1996   | Manchester United
       5 | 1996-1997   | Manchester United
       6 | 1997-1998   | Arsenal
       7 | 1998-1999   | Manchester United
       8 | 1999-2000   | Manchester United
       9 | 2000-2001   | Manchester United
      10 | 2001-2002   | Arsenal
      11 | 2002-2003   | Manchester United
      12 | 2003-2004   | Arsenal
      13 | 2004-2005   | Chelsea
      14 | 2005-2006   | Chelsea
      15 | 2006-2007   | Manchester United
      16 | 2007-2008   | Manchester United
      17 | 2008-2009   | Manchester United
      18 | 2009-2010   | Chelsea
      19 | 2010-2011   | Manchester United
      20 | 2011-2012   | Manchester City
      21 | 2012-2013   | Manchester United
      22 | 2013-2014   | Manchester City
      23 | 2014-2015   | Chelsea
      24 | 2015-2016   | Leicester City
      25 | 2016-2017   | 
(25 rows)

Parent topic:SELECT