*** sql.sgml Mon Jan 8 19:09:51 2001
--- sql.sgml.modi.3 Mon Jan 8 19:08:49 2001
***************
*** 1029,1034 ****
--- 1029,1274 ----
named attributes have to be equal). Finally we project out all
columns but S.SNAME and P.PNAME.
+
+
+ Another way to perform joins is to use the SQL JOIN syntax as follows:
+
+ select sname, pname from supplier
+ JOIN sells USING (sno)
+ JOIN part USING (pno);
+
+ giving again:
+
+ sname | pname
+ -------+-------
+ Smith | Screw
+ Adams | Screw
+ Smith | Nut
+ Blake | Nut
+ Adams | Bolt
+ Blake | Bolt
+ Jones | Cam
+ Blake | Cam
+ (8 rows)
+
+
+
+
+ A joined table, created using JOIN syntax, is a table reference list
+ item that occurs in a FROM clause and before any WHERE, GROUP BY,
+ or HAVING clause. Other table references, including table names or
+ other JOIN clauses, may be included in the FROM clause if separated
+ by commas. JOINed tables are logically like any other
+ table listed in the FROM clause.
+
+
+
+ JOINs of all types can be chained together or nested where either or both of
+ T1 and
+ T2 may be JOINed tables.
+ A Qualified JOIN may be JOINed to another table (or JOINed table)
+ following its join specification, which consists of either an
+ ON search condition or
+ USING ( join column list ) clause.
+ Parenthesis can be used around JOIN clauses to control the order
+ of JOINs which are otherwise processed left to right.
+
+
+
+ Join Types
+
+ CROSS JOIN
+
+
+ T1
+ CROSS
+ JOIN
+ T2
+
+
+
+ A cross join takes two tables T1 and T2 having N and M rows
+ respectively, and returns a joined table containing a cross
+ product, NxM, of joined rows. For each row R1 of T1, each row
+ R2 of T2 is joined with R1 to yield a joined table row JR
+ consisting of all fields in R1 and R2.
+
+
+
+
+
+ Qualified JOINs
+
+
+ T1
+
+
+ INNER
+
+
+ LEFT
+ RIGHT
+ FULL
+
+ OUTER
+
+
+
+ JOIN
+ T2
+
+
+ ON search condition
+ USING ( join column list )
+
+
+ ...
+
+
+
+ Only the qualified JOIN types can use ON or USING clauses. The ON clause
+ takes a search condition, which is the same
+ as in a WHERE clause. The USING clause takes a comma-separated list of
+ column names, which the joined tables must have in common, and joins
+ the tables on those columns, resulting in a joined table having one
+ column for each common column and all of the other columns from both tables.
+
+
+
+
+
+
+
+ INNER
+ JOIN
+
+
+
+
+ For each row R1 of T1, the joined table has a row for each row
+ in T2 that satisfies the join specification with R1.
+
+
+
+ The words INNER and OUTER are optional for all JOINs.
+ INNER is the default. LEFT, RIGHT, and FULL are for
+ OUTER JOINs only.
+
+
+
+
+
+
+
+ LEFT
+ OUTER
+ JOIN
+
+
+
+
+ First, an INNER JOIN is performed.
+ Then, where a row in T1 does not satisfy the join specification
+ with any row in T2, a joined row is returned with null fields in
+ columns from T2.
+
+
+
+ The joined table unconditionally has a row for each row in T1.
+
+
+
+
+
+
+
+ RIGHT
+ OUTER
+ JOIN
+
+
+
+
+ Rule 1: For each row R2 of T2, the joined table has a row for each
+ row in T1 that satisfies the join specification with R2 (transposed
+ [INNER] JOIN).
+ Rule 2: Where a row in T2 does not satisfy the join specification
+ with any row in T1, a joined row is returned with null fields in
+ columns from T1.
+
+
+
+ The joined table unconditionally has a row for each row in T2.
+
+
+
+
+
+
+
+ FULL
+ OUTER
+ JOIN
+
+
+
+
+ First, a LEFT [OUTER] JOIN is performed.
+ Then, Rule 2 of a RIGHT [OUTER] JOIN is performed.
+
+
+
+ The joined table unconditionally has a row for every row of T1
+ and a row for every row of T2.
+
+
+
+
+
+
+
+
+
+
+
+ NATURAL JOINs
+
+
+ T1
+
+ NATURAL
+
+ INNER
+
+
+ LEFT
+ RIGHT
+ FULL
+
+ OUTER
+
+
+
+ JOIN
+ T2
+
+
+
+ A natural join creates a joined table where every pair of matching
+ column names between the two tables are merged into one column. The
+ join specification is effectively a USING clause containing all the
+ common column names and is otherwise like a Qualified JOIN.
+
+
+
+
+
+ UNION JOIN
+ Deprecated.
+
+
+
+