185
185
A table reference can be a table name (possibly schema-qualified),
186
or a derived table such as a subquery, a table join, or complex
187
combinations of these. If more than one table reference is listed
191
> clause they are cross-joined (see below)
192
to form the intermediate virtual table that can then be subject to
186
or a derived table such as a subquery, a <TT
190
complex combinations of these. If more than one table reference is
194
> clause, the tables are cross-joined
195
(that is, the Cartesian product of their rows is formed; see below).
196
The result of the <TT
199
> list is an intermediate virtual
200
table that can then be subject to
193
201
transformations by the <TT
249
257
> A joined table is a table derived from two other (real or
250
258
derived) tables according to the rules of the particular join
251
259
type. Inner, outer, and cross-joins are available.
260
The general syntax of a joined table is
288
Joins of all types can be chained together, or nested: either or
300
> can be joined tables. Parentheses
301
can be used around <TT
304
> clauses to control the join
305
order. In the absence of parentheses, <TT
400
It is also equivalent to
423
> This latter equivalence does not hold exactly when more than two
424
tables appear, because <TT
427
> binds more tightly than
493
> in the first case but not
473
615
>, as explained in detail below.
618
> The possible types of qualified join are:
632
> For each row R1 of T1, the joined table has a row for each
633
row in T2 that satisfies the join condition with R1.
646
> First, an inner join is performed. Then, for each row in
647
T1 that does not satisfy the join condition with any row in
648
T2, a joined row is added with null values in columns of
649
T2. Thus, the joined table always has at least
650
one row for each row in T1.
656
>RIGHT OUTER JOIN</TT
663
> First, an inner join is performed. Then, for each row in
664
T2 that does not satisfy the join condition with any row in
665
T1, a joined row is added with null values in columns of
666
T1. This is the converse of a left join: the result table
667
will always have a row for each row in T2.
677
> First, an inner join is performed. Then, for each row in
678
T1 that does not satisfy the join condition with any row in
679
T2, a joined row is added with null values in columns of
680
T2. Also, for each row of T2 that does not satisfy the
681
join condition with any row in T1, a joined row with null
682
values in the columns of T1 is added.
499
> expression evaluates to true for them.
713
> expression evaluates to true.
505
> is a shorthand notation: it takes a
506
comma-separated list of column names, which the joined tables
507
must have in common, and forms a join condition specifying
508
equality of each of these pairs of columns. Furthermore, the
719
> clause is a shorthand that allows you to take
720
advantage of the specific situation where both sides of the join use
721
the same name for the joining column(s). It takes a
722
comma-separated list of the shared column names
723
and forms a join condition that includes an equality comparison
724
for each one. For example, joining <TT
739
the join condition <TT
767
> Furthermore, the output of <TT
512
> has one column for each of
513
the equated pairs of input columns, followed by the
514
remaining columns from each table. Thus, <TT
518
> is equivalent to <TT
521
t1.b = t2.b AND t1.c = t2.c)</TT
522
> with the exception that
526
> is used there will be two columns
771
redundant columns: there is no need to print both of the matched
772
columns, since they must have equal values. While <TT
776
> produces all columns from <TT
540
> there will be only one of each
541
(and they will appear first if <TT
791
output column for each of the listed column pairs (in the listed
792
order), followed by any remaining columns from <TT
798
followed by any remaining columns from <TT
576
> The possible types of qualified join are:
590
> For each row R1 of T1, the joined table has a row for each
591
row in T2 that satisfies the join condition with R1.
604
> First, an inner join is performed. Then, for each row in
605
T1 that does not satisfy the join condition with any row in
606
T2, a joined row is added with null values in columns of
607
T2. Thus, the joined table always has at least
608
one row for each row in T1.
614
>RIGHT OUTER JOIN</TT
621
> First, an inner join is performed. Then, for each row in
622
T2 that does not satisfy the join condition with any row in
623
T1, a joined row is added with null values in columns of
624
T1. This is the converse of a left join: the result table
625
will always have a row for each row in T2.
635
> First, an inner join is performed. Then, for each row in
636
T1 that does not satisfy the join condition with any row in
637
T2, a joined row is added with null values in columns of
638
T2. Also, for each row of T2 that does not satisfy the
639
join condition with any row in T1, a joined row with null
640
values in the columns of T1 is added.
651
> Joins of all types can be chained together or nested: either or
663
> can be joined tables. Parentheses
664
can be used around <TT
667
> clauses to control the join
668
order. In the absence of parentheses, <TT
844
> is reasonably safe from column changes
845
in the joined relations since only the listed columns
849
> is considerably more risky since
850
any schema changes to either relation that cause a new matching
851
column name to be present will cause the join to combine that new
675
860
> To put this together, assume we have tables <TT
added
removed
doc/src/sgml/html/queries-table-expressions.html
