2020-05-24, 17:49
Here’s a short overview of SQL joins, find the source code of the document attached to the PDF (use Adobe Reader to access it).
SQL Joins
\documentclass[12pt]{article}
\usepackage{xcolor}
\usepackage{arev}
\usepackage{attachfile}
\usepackage{tikz}
\usetikzlibrary{shapes,snakes}
\def\firstcircle{(0,0) circle (2cm)}
\def\secondcircle{(0:3cm) circle (2cm)}
\colorlet{circle edge}{blue!50}
\colorlet{circle area}{blue!20}
\colorlet{white area}{white}
\tikzset{
filled/.style={fill=circle area, draw=circle edge, thick},
outline/.style={draw=circle edge, thick},
white/.style={fill=white area, draw=circle edge, very thick}
}
\begin{document}
\textattachfile{\jobname.tex}{\LaTeX~Code}
\section{Inner Join}
Inner joins return those records, that are present in table 1 and table 2.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Inner Join
\begin{scope}
\clip \firstcircle;
\fill[filled] \secondcircle;
\end{scope}
\draw[outline] \firstcircle node {A};
\draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
\section{Left Join}
Every record from table 1 is returned, regardless if it has a matching record in table 2. If the record has a matching record in table 2 this record from table 2 is returned as well.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Join
\begin{scope}
\clip \firstcircle;
\draw[filled] \firstcircle node {A}
\secondcircle;
\end{scope}
\draw[outline] \firstcircle
\secondcircle node {B};
\end{tikzpicture}
\end{center}
\clearpage
\section{Left Outer Join}
Those records from table 1 are returned, that do not have a match in table 2.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Outer Join
\draw[filled] \firstcircle node {A};
\draw[white] \secondcircle node {B};
\draw[outline] \firstcircle node {A};
\end{tikzpicture}
\end{center}
\section{Right Join}
Every record from table 2 is returned, regardless if it has a matching record in table 1. If the record has a matching record in table 1 this record from table 1 is returned as well.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join
\begin{scope}
\clip \secondcircle;
\draw[filled] \firstcircle \secondcircle node {B};
\end{scope}
\draw[outline] \firstcircle node {A} \secondcircle;
\end{tikzpicture}
\end{center}
\clearpage
\section{Right Outer Join}
Those records from table 2 are returned, that do not have a match in table 1.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join 2
\draw[filled] \secondcircle node {B};
\draw[white] \firstcircle node {A};
\draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
\section{Theta Join}
Every row from dataset 1 is multiplied by every row of dataset 2. Usually a mistake unless you really want this Cartesian product.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Theta
\draw[filled] \firstcircle node {A} \secondcircle node {B};
\end{tikzpicture}
\end{center}
\clearpage
\section{Full Outer}
Returns all records from table 1 and table 2 regardless if they have matches in the other table. Records, that do match, are linked together via key.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Full Outer
\draw[filled] \firstcircle node {A};
\draw[filled] \secondcircle node {B};
\begin{scope}
\clip \firstcircle;
\fill[white] \secondcircle;
\draw[outline] \secondcircle;
\draw[outline] \firstcircle;
\end{scope}
\end{tikzpicture}
\end{center}
\end{document} |
\documentclass[12pt]{article}
\usepackage{xcolor}
\usepackage{arev}
\usepackage{attachfile}
\usepackage{tikz}
\usetikzlibrary{shapes,snakes}
\def\firstcircle{(0,0) circle (2cm)}
\def\secondcircle{(0:3cm) circle (2cm)}
\colorlet{circle edge}{blue!50}
\colorlet{circle area}{blue!20}
\colorlet{white area}{white}
\tikzset{
filled/.style={fill=circle area, draw=circle edge, thick},
outline/.style={draw=circle edge, thick},
white/.style={fill=white area, draw=circle edge, very thick}
}
\begin{document}
\textattachfile{\jobname.tex}{\LaTeX~Code}
\section{Inner Join}
Inner joins return those records, that are present in table 1 and table 2.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Inner Join
\begin{scope}
\clip \firstcircle;
\fill[filled] \secondcircle;
\end{scope}
\draw[outline] \firstcircle node {A};
\draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
\section{Left Join}
Every record from table 1 is returned, regardless if it has a matching record in table 2. If the record has a matching record in table 2 this record from table 2 is returned as well.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Join
\begin{scope}
\clip \firstcircle;
\draw[filled] \firstcircle node {A}
\secondcircle;
\end{scope}
\draw[outline] \firstcircle
\secondcircle node {B};
\end{tikzpicture}
\end{center}
\clearpage
\section{Left Outer Join}
Those records from table 1 are returned, that do not have a match in table 2.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Left Outer Join
\draw[filled] \firstcircle node {A};
\draw[white] \secondcircle node {B};
\draw[outline] \firstcircle node {A};
\end{tikzpicture}
\end{center}
\section{Right Join}
Every record from table 2 is returned, regardless if it has a matching record in table 1. If the record has a matching record in table 1 this record from table 1 is returned as well.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join
\begin{scope}
\clip \secondcircle;
\draw[filled] \firstcircle \secondcircle node {B};
\end{scope}
\draw[outline] \firstcircle node {A} \secondcircle;
\end{tikzpicture}
\end{center}
\clearpage
\section{Right Outer Join}
Those records from table 2 are returned, that do not have a match in table 1.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Right Join 2
\draw[filled] \secondcircle node {B};
\draw[white] \firstcircle node {A};
\draw[outline] \secondcircle node {B};
\end{tikzpicture}
\end{center}
\section{Theta Join}
Every row from dataset 1 is multiplied by every row of dataset 2. Usually a mistake unless you really want this Cartesian product.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Theta
\draw[filled] \firstcircle node {A} \secondcircle node {B};
\end{tikzpicture}
\end{center}
\clearpage
\section{Full Outer}
Returns all records from table 1 and table 2 regardless if they have matches in the other table. Records, that do match, are linked together via key.
\begin{center}
\begin{tikzpicture}[scale=1, every node/.style={scale=1}] % Full Outer
\draw[filled] \firstcircle node {A};
\draw[filled] \secondcircle node {B};
\begin{scope}
\clip \firstcircle;
\fill[white] \secondcircle;
\draw[outline] \secondcircle;
\draw[outline] \firstcircle;
\end{scope}
\end{tikzpicture}
\end{center}
\end{document}
Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined.
Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.
More Posts - Website