uweziegenhagen.de

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

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

SELECT n.Name, a.Adresse FROM Namen n JOIN Adressen a ON n.ID = a.NameID ;

SELECT n.Name, a.Adresse FROM Namen n
LEFT JOIN (SELECT MAX(ID) ID, MIN(NameID) NameID, MIN(Adresse) Adresse FROM Adressen GROUP BY NameID) a
ON n.ID = a.NameID

# -*- coding: utf-8 -*-
 
from sqlalchemy import create_engine
import pymysql
import pandas as pd
 
sqlEngine       = create_engine('mysql+pymysql://nwread:northwind@192.168.0.60/northwind', pool_recycle=3600)
dbConnection    = sqlEngine.connect()
frame           = pd.read_sql("select * from products", dbConnection);
 
pd.set_option('display.expand_frame_repr', False)
print(frame)
 
frame.to_excel('r:/abc.xlsx')
 
dbConnection.close()

import pandas as pd
import pymysql
from sqlalchemy import create_engine
 
engine = create_engine('mysql+pymysql://<user>:<password>@<host>[:<port>]/<dbname>')
df = pd.read_sql_query('SELECT * FROM table', engine)
df.head()

SELECT
   N,
   SIGN(N & 1) AS Bit1,
   SIGN(N & 2) AS Bit2,
   SIGN(N & 4) AS Bit3,
   SIGN(N & 8) AS Bit4,
   SIGN(N & 16) AS Bit5,
   SIGN(N & 32) AS Bit6,
   SIGN(N & 64) AS Bit7,
   SIGN(N & 128) AS Bit8
FROM (
   SELECT 511
) TestData(N)

DROP FUNCTION IF EXISTS fs_getname;
DELIMITER $$
CREATE FUNCTION fs_getname(nummer INT, typ CHAR)
 RETURNS VARCHAR(100)
 NOT DETERMINISTIC
 BEGIN
	IF typ = 'c' THEN
		RETURN (SELECT TRIM(CONCAT(COALESCE(Vorname,"")," ",COALESCE(Name," "))) FROM Stammdaten WHERE  ID = nummer);
	ELSEIF typ = 'f' THEN
		RETURN (SELECT COALESCE(Vorname,"") FROM Stammdaten WHERE  ID = nummer);
	ELSEIF typ = 'l' THEN
		RETURN (SELECT COALESCE(Name,"") FROM Stammdaten WHERE  ID = nummer);		
	END IF;
END$$
DELIMITER ;