sql-ecology-lesson
sql-ecology-lesson copied to clipboard
datacarpentry
add diagrams for SQL statements
It could be useful to add diagrams for WHERE and JOIN statements.
For WHERE we can have boxes that show the selection of subsets of the data.
For JOIN there are good figures available with Venn diagrams, although they include more information, such as with INNER and OUTER joins, e.g.
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
I have a this as a svg:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- Created with Inkscape (http://www.inkscape.org/) -->
<svg
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:cc="http://creativecommons.org/ns#"
xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
xmlns:svg="http://www.w3.org/2000/svg"
xmlns="http://www.w3.org/2000/svg"
xmlns:sodipodi="http://sodipodi.sourceforge.net/DTD/sodipodi-0.dtd"
xmlns:inkscape="http://www.inkscape.org/namespaces/inkscape"
width="12cm"
height="13cm"
viewBox="0 0 425.19684 460.63002"
id="svg2"
version="1.1"
inkscape:version="0.91 r13725"
sodipodi:docname="joins.svg">
<defs
id="defs4" />
<sodipodi:namedview
id="base"
pagecolor="#ffffff"
bordercolor="#666666"
borderopacity="1.0"
inkscape:pageopacity="0.0"
inkscape:pageshadow="2"
inkscape:zoom="1.979899"
inkscape:cx="124.62963"
inkscape:cy="295.31339"
inkscape:document-units="px"
inkscape:current-layer="layer1"
showgrid="false"
units="cm"
inkscape:window-width="2546"
inkscape:window-height="1426"
inkscape:window-x="1926"
inkscape:window-y="6"
inkscape:window-maximized="0" />
<metadata
id="metadata7">
<rdf:RDF>
<cc:Work
rdf:about="">
<dc:format>image/svg+xml</dc:format>
<dc:type
rdf:resource="http://purl.org/dc/dcmitype/StillImage" />
<dc:title></dc:title>
</cc:Work>
</rdf:RDF>
</metadata>
<g
inkscape:label="Calque 1"
inkscape:groupmode="layer"
id="layer1"
transform="translate(0,-591.73231)">
<g
id="g4678"
transform="translate(0,92.00002)">
<path
inkscape:connector-curvature="0"
id="path3338-2"
d="m 49.366649,530.13861 a 35.402855,35.402855 0 0 0 -35.402722,35.40272 35.402855,35.402855 0 0 0 35.402722,35.40273 35.402855,35.402855 0 0 0 15.346083,-3.53223 35.402855,35.402855 0 0 1 -20.089049,-31.8705 35.402855,35.402855 0 0 1 20.056626,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-7"
d="m 64.644419,533.64894 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.089058,31.87051 35.402855,35.402855 0 0 0 20.057557,-31.87051 35.402855,35.402855 0 0 0 -20.089055,-31.87051 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-8"
d="m 79.932843,530.13861 a 35.402855,35.402855 0 0 1 35.402727,35.40272 35.402855,35.402855 0 0 1 -35.402727,35.40273 35.402855,35.402855 0 0 1 -15.346085,-3.53223 35.402855,35.402855 0 0 0 20.089051,-31.8705 35.402855,35.402855 0 0 0 -20.056628,-31.86958 35.402855,35.402855 0 0 1 15.313662,-3.53314 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403"
y="612.40015"
x="33.155495"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="612.40015"
x="33.155495"
id="tspan4405"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407"
y="621.77515"
x="33.155495"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409"
y="631.15015"
x="33.155495"
sodipodi:role="line"><tspan
id="tspan4641"
style="font-weight:bold">LEFT JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411"
y="640.52515"
x="33.155495"
sodipodi:role="line">ON A.id = B.id</tspan></text>
</g>
<g
id="g4700"
transform="translate(0,98.38933)">
<path
inkscape:connector-curvature="0"
id="path3338-3-7-0"
d="M 49.366649,658.06708 A 35.402855,35.402855 0 0 0 13.963927,693.4698 35.402855,35.402855 0 0 0 49.366649,728.87253 35.402855,35.402855 0 0 0 64.712732,725.3403 35.402855,35.402855 0 0 1 44.623683,693.4698 35.402855,35.402855 0 0 1 64.680309,661.60022 35.402855,35.402855 0 0 0 49.366649,658.06708 Z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-2-6-6"
d="m 64.644419,661.57741 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.089058,31.87051 35.402855,35.402855 0 0 0 20.057557,-31.87051 35.402855,35.402855 0 0 0 -20.089055,-31.87051 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-1-4-1"
d="M 79.932843,658.06708 A 35.402855,35.402855 0 0 1 115.33557,693.4698 35.402855,35.402855 0 0 1 79.932843,728.87253 35.402855,35.402855 0 0 1 64.586758,725.3403 35.402855,35.402855 0 0 0 84.675809,693.4698 35.402855,35.402855 0 0 0 64.619181,661.60022 35.402855,35.402855 0 0 1 79.932843,658.06708 Z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-3"
y="740.26758"
x="33.155495"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="740.26758"
x="33.155495"
id="tspan4405-6"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-5"
y="749.64258"
x="33.155495"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-6"
y="759.01758"
x="33.155495"
sodipodi:role="line"><tspan
id="tspan4639"
style="font-weight:bold">LEFT JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-7"
y="768.39258"
x="33.155495"
sodipodi:role="line">ON A.id = B.id</tspan><tspan
id="tspan4637"
style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="777.76758"
x="33.155495"
sodipodi:role="line">WHERE B.id IS NULL</tspan></text>
</g>
<g
id="g4737"
transform="translate(0,114.19513)">
<path
inkscape:connector-curvature="0"
id="path3338-3"
d="m 49.366649,785.99556 a 35.402855,35.402855 0 0 0 -35.402722,35.40272 35.402855,35.402855 0 0 0 35.402722,35.40273 35.402855,35.402855 0 0 0 15.346083,-3.53223 35.402855,35.402855 0 0 1 -20.089049,-31.8705 35.402855,35.402855 0 0 1 20.056626,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-2"
d="M 64.644419,789.50589 A 35.402855,35.402855 0 0 0 44.586859,821.3764 35.402855,35.402855 0 0 0 64.675917,853.24691 35.402855,35.402855 0 0 0 84.733474,821.3764 35.402855,35.402855 0 0 0 64.644419,789.50589 Z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-1"
d="m 79.932843,785.99556 a 35.402855,35.402855 0 0 1 35.402727,35.40272 35.402855,35.402855 0 0 1 -35.402727,35.40273 35.402855,35.402855 0 0 1 -15.346085,-3.53223 35.402855,35.402855 0 0 0 20.089051,-31.8705 35.402855,35.402855 0 0 0 -20.056628,-31.86958 35.402855,35.402855 0 0 1 15.313662,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-3-0"
y="868.05188"
x="33.155495"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="868.05188"
x="33.155495"
id="tspan4405-6-5"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-5-6"
y="877.42688"
x="33.155495"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-6-5"
y="886.80188"
x="33.155495"
sodipodi:role="line"><tspan
id="tspan4668"
style="font-weight:bold">FULL OUTER JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-7-6"
y="896.17688"
x="33.155495"
sodipodi:role="line">ON A.id = B.id</tspan></text>
</g>
<g
id="g4748"
transform="translate(147.35602,-157.46764)">
<path
inkscape:connector-curvature="0"
id="path3338-3-00"
d="m 49.366649,913.92403 a 35.402855,35.402855 0 0 0 -35.402722,35.40272 35.402855,35.402855 0 0 0 35.402722,35.40273 35.402855,35.402855 0 0 0 15.346083,-3.53223 35.402855,35.402855 0 0 1 -20.089049,-31.8705 35.402855,35.402855 0 0 1 20.056626,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-2-0"
d="m 64.644419,917.43436 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.089058,31.87051 35.402855,35.402855 0 0 0 20.057557,-31.87051 35.402855,35.402855 0 0 0 -20.089055,-31.87051 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-1-3"
d="m 79.932843,913.92403 a 35.402855,35.402855 0 0 1 35.402727,35.40272 35.402855,35.402855 0 0 1 -35.402727,35.40273 35.402855,35.402855 0 0 1 -15.346085,-3.53223 35.402855,35.402855 0 0 0 20.089051,-31.8705 35.402855,35.402855 0 0 0 -20.056628,-31.86958 35.402855,35.402855 0 0 1 15.313662,-3.53314 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-3-8"
y="997.3717"
x="33.155495"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="997.3717"
x="33.155495"
id="tspan4405-6-4"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-5-68"
y="1006.7467"
x="33.155495"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-6-4"
y="1016.1217"
x="33.155495"
sodipodi:role="line"><tspan
id="tspan4670"
style="font-weight:bold">INNER JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-7-8"
y="1025.4967"
x="33.155495"
sodipodi:role="line">ON A.id = B.id</tspan></text>
</g>
<g
id="g4689"
transform="translate(75.256383,89.695439)">
<path
inkscape:connector-curvature="0"
id="path3338-3-0"
d="m 268.82232,530.13861 a 35.402855,35.402855 0 0 0 -35.40272,35.40272 35.402855,35.402855 0 0 0 35.40272,35.40273 35.402855,35.402855 0 0 0 15.34609,-3.53223 35.402855,35.402855 0 0 1 -20.08905,-31.8705 35.402855,35.402855 0 0 1 20.05662,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-2-2"
d="m 284.10009,533.64894 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.08906,31.87051 35.402855,35.402855 0 0 0 20.05756,-31.87051 35.402855,35.402855 0 0 0 -20.08906,-31.87051 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-1-6"
d="m 299.38852,530.13861 a 35.402855,35.402855 0 0 1 35.40272,35.40272 35.402855,35.402855 0 0 1 -35.40272,35.40273 35.402855,35.402855 0 0 1 -15.34609,-3.53223 35.402855,35.402855 0 0 0 20.08905,-31.8705 35.402855,35.402855 0 0 0 -20.05662,-31.86958 35.402855,35.402855 0 0 1 15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-4"
y="612.40015"
x="253.05173"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="612.40015"
x="253.05173"
id="tspan4405-7"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-3"
y="621.77515"
x="253.05173"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-5"
y="631.15015"
x="253.05173"
sodipodi:role="line"><tspan
id="tspan4643"
style="font-weight:bold">RIGHT JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-3"
y="640.52515"
x="253.05173"
sodipodi:role="line">ON A.id = B.id</tspan></text>
</g>
<g
id="g4712"
transform="translate(75.256383,98.38933)">
<path
inkscape:connector-curvature="0"
id="path3338"
d="m 268.82232,658.06708 a 35.402855,35.402855 0 0 0 -35.40272,35.40272 35.402855,35.402855 0 0 0 35.40272,35.40273 35.402855,35.402855 0 0 0 15.34609,-3.53223 35.402855,35.402855 0 0 1 -20.08905,-31.8705 35.402855,35.402855 0 0 1 20.05662,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9"
d="m 284.10009,661.57741 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.08906,31.87051 35.402855,35.402855 0 0 0 20.05756,-31.87051 35.402855,35.402855 0 0 0 -20.08906,-31.87051 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29"
d="m 299.38852,658.06708 a 35.402855,35.402855 0 0 1 35.40272,35.40272 35.402855,35.402855 0 0 1 -35.40272,35.40273 35.402855,35.402855 0 0 1 -15.34609,-3.53223 35.402855,35.402855 0 0 0 20.08905,-31.8705 35.402855,35.402855 0 0 0 -20.05662,-31.86958 35.402855,35.402855 0 0 1 15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-3-9"
y="740.26758"
x="253.05173"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="740.26758"
x="253.05173"
id="tspan4405-6-3"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-5-7"
y="749.64258"
x="253.05173"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-6-9"
y="759.01758"
x="253.05173"
sodipodi:role="line"><tspan
id="tspan4664"
style="font-weight:bold">RIGHT JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-7-9"
y="768.39258"
x="253.05173"
sodipodi:role="line">ON A.id = B.id</tspan><tspan
id="tspan4666"
style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="777.76758"
x="253.05173"
sodipodi:role="line">WHERE A.id IS NULL</tspan></text>
</g>
<g
id="g4724"
transform="translate(75.256383,114.19513)">
<path
inkscape:connector-curvature="0"
id="path3338-3-7"
d="m 268.82232,785.99556 a 35.402855,35.402855 0 0 0 -35.40272,35.40272 35.402855,35.402855 0 0 0 35.40272,35.40273 35.402855,35.402855 0 0 0 15.34609,-3.53223 35.402855,35.402855 0 0 1 -20.08905,-31.8705 35.402855,35.402855 0 0 1 20.05662,-31.86958 35.402855,35.402855 0 0 0 -15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
inkscape:connector-curvature="0"
id="path3338-2-9-2-6"
d="m 284.10009,789.50589 a 35.402855,35.402855 0 0 0 -20.05756,31.87051 35.402855,35.402855 0 0 0 20.08906,31.87051 35.402855,35.402855 0 0 0 20.05756,-31.87051 35.402855,35.402855 0 0 0 -20.08906,-31.87051 z"
style="opacity:1;fill:none;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1" />
<path
id="path3338-29-1-4"
d="m 299.38852,785.99556 a 35.402855,35.402855 0 0 1 35.40272,35.40272 35.402855,35.402855 0 0 1 -35.40272,35.40273 35.402855,35.402855 0 0 1 -15.34609,-3.53223 35.402855,35.402855 0 0 0 20.08905,-31.8705 35.402855,35.402855 0 0 0 -20.05662,-31.86958 35.402855,35.402855 0 0 1 15.31366,-3.53314 z"
style="opacity:1;fill:#c1cfde;fill-opacity:1;stroke:#2f2b41;stroke-width:0.4742966;stroke-opacity:1"
inkscape:connector-curvature="0" />
<text
sodipodi:linespacing="125%"
id="text4403-3-0-6"
y="868.05188"
x="253.05173"
style="font-style:normal;font-weight:normal;font-size:7.50000143px;line-height:125%;font-family:sans-serif;letter-spacing:0px;word-spacing:0px;fill:#333333;fill-opacity:1;stroke:none;stroke-width:1px;stroke-linecap:butt;stroke-linejoin:miter;stroke-opacity:1"
xml:space="preserve"><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="868.05188"
x="253.05173"
id="tspan4405-6-5-0"
sodipodi:role="line">SELECT * </tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4407-5-6-4"
y="877.42688"
x="253.05173"
sodipodi:role="line">FROM A</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4409-6-5-9"
y="886.80188"
x="253.05173"
sodipodi:role="line"><tspan
id="tspan4672"
style="font-weight:bold">FULL OUTER JOIN</tspan> B</tspan><tspan
style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
id="tspan4411-7-6-2"
y="896.17688"
x="253.05173"
sodipodi:role="line">ON A.id = B.id</tspan><tspan
id="tspan4674"
style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="905.55188"
x="253.05173"
sodipodi:role="line">WHERE A.id IS NULL</tspan><tspan
id="tspan4676"
style="font-style:normal;font-variant:normal;font-weight:bold;font-stretch:normal;font-size:7.50000143px;font-family:'DejaVu Sans Mono';-inkscape-font-specification:'DejaVu Sans Mono';fill:#333333"
y="914.92688"
x="253.05173"
sodipodi:role="line">OR B.id IS NULL</tspan></text>
</g>
</g>
</svg>
@tpoisot Now that episode 03 has been updated (fixed the alias section) Would you like to share your pretty svg graph?
This is an old issue, but I think it is worth bringing up again. We need diagrams in Episode 03 to explain the different types of joins. I would suggest using a simplified version of @tpoisot SVG. Specifically, I think we should have a diagram of what a cross product means after the second paragraph of the lesson, as a visual explanation of why the cross product of tables is usually not what a user wants.
Then, under "Different Join Types", we should have 4 Venn diagrams showing INNER JOIN (aka JOIN), LEFT JOIN, RIGHT JOIN, and OUTER JOIN. We should also note that SQLite doesn't implement RIGHT JOIN or OUTER JOIN, but they can be implemented using LEFT JOIN (we could link to external documentation e.g. https://www.sqlitetutorial.net/sqlite-full-outer-join/).
I think the other three diagrams in the SVG above (the ones with a WHERE clause) might be outside the scope of the lesson. We don't cover joins with WHERE clauses in much detail, and I am concerned that having too many Venn diagrams might be confusing for beginner learners.
Note to maintainers: I am about to be certified as a maintainer on this lesson, so I can work on this one and make a PR when it's ready!
Suggestion for JOIN diagrams - repurpose these: https://datacarpentry.org/python-ecology-lesson/05-merging-data/index.html
