Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/queries.sgml`
b6726cad6cbd7ac1e97198f6291559e1b0e7113173d169ce0000000100000fa1
   because searching descendant tables is now always the default behavior.
    However, it is supported for compatibility with older releases.
   </para>

   <sect3 id="queries-join">
    <title>Joined Tables</title>

    <indexterm zone="queries-join">
     <primary>join</primary>
    </indexterm>

    <para>
     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
<synopsis>
<replaceable>T1</replaceable> <replaceable>join_type</replaceable> <replaceable>T2</replaceable> <optional> <replaceable>join_condition</replaceable> </optional>
</synopsis>
     Joins of all types can be chained together, or nested: either or
     both <replaceable>T1</replaceable> and
     <replaceable>T2</replaceable> can be joined tables.  Parentheses
     can be used around <literal>JOIN</literal> clauses to control the join
     order.  In the absence of parentheses, <literal>JOIN</literal> clauses
     nest left-to-right.
    </para>

    <variablelist>
     <title>Join Types</title>

     <varlistentry>
      <term>Cross join
      <indexterm>
       <primary>join</primary>
       <secondary>cross</secondary>
      </indexterm>

      <indexterm>
       <primary>cross join</primary>
      </indexterm>
      </term>

      <listitem>
<synopsis>
<replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
</synopsis>

       <para>
        For every possible combination of rows from
        <replaceable>T1</replaceable> and
        <replaceable>T2</replaceable> (i.e., a Cartesian product),
        the joined table will contain a
        row consisting of all columns in <replaceable>T1</replaceable>
        followed by all columns in <replaceable>T2</replaceable>.  If
        the tables have N and M rows respectively, the joined
        table will have N * M rows.
       </para>

       <para>
        <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
        <replaceable>T2</replaceable></literal> is equivalent to
        <literal>FROM <replaceable>T1</replaceable> INNER JOIN
        <replaceable>T2</replaceable> ON TRUE</literal> (see below).
        It is also equivalent to
        <literal>FROM <replaceable>T1</replaceable>,
        <replaceable>T2</replaceable></literal>.
        <note>
        <para>
         This latter equivalence does not hold exactly when more than two
         tables appear, because <literal>JOIN</literal> binds more tightly than
         comma.  For example
         <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
         <replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
         ON <replaceable>condition</replaceable></literal>
         is not the same as
         <literal>FROM <replaceable>T1</replaceable>,
         <replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
         ON <replaceable>condition</replaceable></literal>
         because the <replaceable>condition</replaceable> can
         reference <replaceable>T1</replaceable> in the first case but not
         the second.
        </para>
        </note>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualified joins
      <indexterm>
       <primary>join</primary>
       <secondary>outer</secondary>
      </indexterm>

      <indexterm>
       <primary>outer join</primary>
      </indexterm>
      </term>

      <listitem>
<synopsis>
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
<replaceable>T1</replaceable> NATURAL { <optional>INNER</optional>
Title: SQL Join Types and Syntax
Summary
This section details various types of SQL joins and their syntax. It starts by explaining the general structure of a joined table, noting that joins can be chained or nested, and parentheses can control join order. The text then describes specific join types: Cross join (Cartesian product), which combines every row from one table with every row from another, and Qualified joins, including INNER, LEFT/RIGHT/FULL OUTER joins. For each join type, the syntax is provided along with explanations of how the join operates. The section also mentions the use of ON clauses for specifying join conditions and USING clauses for joining on columns with the same name in both tables. Additionally, it covers NATURAL joins, which automatically join on all columns with the same name in both tables.