Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/queries.sgml`
26c7a5e75d2ee49d88decae897704f001fa9c653e7c214a30000000100000fa0
 <indexterm>
          <primary>join</primary>
          <secondary>right</secondary>
         </indexterm>

         <indexterm>
          <primary>right join</primary>
         </indexterm>
         </term>

         <listitem>
          <para>
           First, an inner join is performed.  Then, for each row in
           T2 that does not satisfy the join condition with any row in
           T1, a joined row is added with null values in columns of
           T1.  This is the converse of a left join: the result table
           will always have a row for each row in T2.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term><literal>FULL OUTER JOIN</literal></term>

         <listitem>
          <para>
           First, an inner join is performed.  Then, for each row in
           T1 that does not satisfy the join condition with any row in
           T2, a joined row is added with null values in columns of
           T2.  Also, for each row of T2 that does not satisfy the
           join condition with any row in T1, a joined row with null
           values in the columns of T1 is added.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
       </para>

       <para>
        The <literal>ON</literal> clause is the most general kind of join
        condition: it takes a Boolean value expression of the same
        kind as is used in a <literal>WHERE</literal> clause.  A pair of rows
        from <replaceable>T1</replaceable> and <replaceable>T2</replaceable> match if the
        <literal>ON</literal> expression evaluates to true.
       </para>

       <para>
        The <literal>USING</literal> clause is a shorthand that allows you to take
        advantage of the specific situation where both sides of the join use
        the same name for the joining column(s).  It takes a
        comma-separated list of the shared column names
        and forms a join condition that includes an equality comparison
        for each one.  For example, joining <replaceable>T1</replaceable>
        and <replaceable>T2</replaceable> with <literal>USING (a, b)</literal> produces
        the join condition <literal>ON <replaceable>T1</replaceable>.a
        = <replaceable>T2</replaceable>.a AND <replaceable>T1</replaceable>.b
        = <replaceable>T2</replaceable>.b</literal>.
       </para>

       <para>
        Furthermore, the output of <literal>JOIN USING</literal> suppresses
        redundant columns: there is no need to print both of the matched
        columns, since they must have equal values.  While <literal>JOIN
        ON</literal> produces all columns from <replaceable>T1</replaceable> followed by all
        columns from <replaceable>T2</replaceable>, <literal>JOIN USING</literal> produces one
        output column for each of the listed column pairs (in the listed
        order), followed by any remaining columns from <replaceable>T1</replaceable>,
        followed by any remaining columns from <replaceable>T2</replaceable>.
       </para>

       <para>
        <indexterm>
         <primary>join</primary>
         <secondary>natural</secondary>
        </indexterm>
        <indexterm>
         <primary>natural join</primary>
        </indexterm>
        Finally, <literal>NATURAL</literal> is a shorthand form of
        <literal>USING</literal>: it forms a <literal>USING</literal> list
        consisting of all column names that appear in both
        input tables.  As with <literal>USING</literal>, these columns appear
        only once in the output table.  If there are no common
        column names, <literal>NATURAL JOIN</literal> behaves like
        <literal>CROSS JOIN</literal>.
       </para>

       <note>
        <para>
         <literal>USING</literal> is reasonably safe from column changes
         in the joined relations since only the listed columns
         are combined.  <literal>NATURAL</literal> is considerably more risky since
Title: SQL Join Conditions: ON, USING, and NATURAL
Summary
This section explains different ways to specify join conditions in SQL. The ON clause is the most general, allowing any Boolean expression. The USING clause is a shorthand for joining on columns with the same name in both tables, creating equality comparisons automatically. It also suppresses redundant columns in the output. NATURAL join is a further shorthand that automatically uses all columns with the same name in both tables. The text describes how each method affects the output columns and warns that NATURAL joins can be risky if table structures change. ON is the most flexible, USING is safer for schema changes, and NATURAL is the most concise but potentially fragile option.