\begin{frame}
  \frametitle{Inner and Outer Joins}

  \begin{goal}{}
    SQL-92 supports the following \emph{join types} (\sql{[..]} is optional):
    \begin{itemize}
    \item{}[\sql{inner}] \sql{join}: \quad usual join
    \item \sql{left} [\sql{outer}] \sql{join}: \quad preserves rows of left table
    \item \sql{right} [\sql{outer}] \sql{join}: \quad preserves rows of right table
    \item \sql{full} [\sql{outer}] \sql{join}: \quad preserves rows of both tables
    \item \sql{cross join}: \quad Cartesian product (all combinations)
    \end{itemize}
  \end{goal}
  \pause

  \begin{exampleblock}{}
    A \emph{join ($\join$) eliminates tuples without partner}.
    \begin{tcenter}
    $%
    \colorbox{rellight}{%
      $
      \begin{array}{|r|r|}
        \hline
        \hd{$A$} & \hd{$B$} \\ \hline
        a_1 & b_1 \\
        a_2 & b_2 \\
        \hline
      \end{array}$%
    }
    \join
    \colorbox{rellight}{%
      $
      \begin{array}{|r|r|}
        \hline
        \hd{$B$} & \hd{$C$} \\ \hline
        b_2 & c_2 \\
        b_3 & c_3 \\
        \hline
      \end{array}$%
    }
    \quad=\quad
    \colorbox{rellight}{%
      $
      \begin{array}{|r|r|r|}
        \hline
        \hd{$A$} & \hd{$B$} & \hd{$C$} \\ \hline
        a_2 & b_2 & c_2 \\
        \hline
      \end{array}$%
    }
    $
    \end{tcenter}
  \end{exampleblock}
  \pause
  
  \begin{exampleblock}{}
    The \emph{left outer join} preserves all tuples in its \emph{left} argument:
    %even if a tuple does not team up with a partner in the join:
    \begin{tcenter}
      $%
      \colorbox{rellight}{%
        $
        \begin{array}{|r|r|}
          \hline
          \hd{$A$} & \hd{$B$} \\ \hline
          a_1 & b_1 \\
          a_2 & b_2 \\
          \hline
        \end{array}$%
      }
      \Louterjoin
      \colorbox{rellight}{%
        $
        \begin{array}{|r|r|}
          \hline
          \hd{$B$} & \hd{$C$} \\ \hline
          b_2 & c_2 \\
          b_3 & c_3 \\
          \hline
        \end{array}$%
      }
      \quad=\quad
      \colorbox{rellight}{%
        $
        \begin{array}{|r|r|r|}
          \hline
          \hd{$A$} & \hd{$B$} & \hd{$C$} \\ \hline
          a_1 & b_1 & \text{(null)} \\
          a_2 & b_2 & c_2 \\
          \hline
        \end{array}$%
      }
      $
    \end{tcenter}
  \end{exampleblock}
\end{frame}