Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/queries.sgml`
2d76f62e91340ab086f73cdcf3a4f8eda3c6b1989d8f45f10000000100000fa1
 <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> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
</synopsis>

       <para>
        The words <literal>INNER</literal> and
        <literal>OUTER</literal> are optional in all forms.
        <literal>INNER</literal> is the default;
        <literal>LEFT</literal>, <literal>RIGHT</literal>, and
        <literal>FULL</literal> imply an outer join.
       </para>

       <para>
        The <firstterm>join condition</firstterm> is specified in the
        <literal>ON</literal> or <literal>USING</literal> clause, or implicitly by
        the word <literal>NATURAL</literal>.  The join condition determines
        which rows from the two source tables are considered to
        <quote>match</quote>, as explained in detail below.
       </para>

       <para>
        The possible types of qualified join are:

       <variablelist>
        <varlistentry>
         <term><literal>INNER JOIN</literal></term>

         <listitem>
          <para>
           For each row R1 of T1, the joined table has a row for each
           row in T2 that satisfies the join condition with R1.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term><literal>LEFT OUTER JOIN</literal>
         <indexterm>
          <primary>join</primary>
          <secondary>left</secondary>
         </indexterm>

         <indexterm>
          <primary>left join</primary>
         </indexterm>
         </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.  Thus, the joined table always has at least
           one row for each row in T1.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term><literal>RIGHT OUTER JOIN</literal>
         <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
Title: SQL Join Types and Their Behaviors
Summary
This section provides a detailed explanation of various SQL join types and their behaviors. It covers INNER JOIN, which combines rows from two tables based on a join condition; LEFT OUTER JOIN, which includes all rows from the left table and matched rows from the right table; RIGHT OUTER JOIN, which includes all rows from the right table and matched rows from the left table; and FULL OUTER JOIN, which includes all rows from both tables, using NULL values where there is no match. The text also explains that the join condition can be specified using ON, USING, or NATURAL clauses. It emphasizes that INNER is the default join type, while LEFT, RIGHT, and FULL imply outer joins. The section provides syntax examples and detailed descriptions of how each join type behaves, including how unmatched rows are handled in outer joins.