\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}