SQLite Complex Joins

Wilbrow, Marc. “Generate all 16 possible 2-variable shaded Venn diagrams in TikZ? Stack Exchange. Dec 11, 2015. https://tex.stackexchange.com/questions/266326/generate-all-16-possible-2-variable-shaded-venn-diagrams-in-tikz
% sqlite3 example.db
sqlite> CREATE TABLE Table_A (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Table_B (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO Table_A (name) VALUES ("name_1");
sqlite> INSERT INTO Table_A (name) VALUES ("name_2");
sqlite> INSERT INTO Table_A (name) VALUES ("name_3");
sqlite> INSERT INTO Table_A (name) VALUES ("name_4")
sqlite> INSERT INTO Table_B (name) VALUES ("name_5");
sqlite> INSERT INTO Table_B (name) VALUES ("name_1");
sqlite> INSERT INTO Table_B (name) VALUES ("name_6");
sqlite> INSERT INTO Table_B (name) VALUES ("name_3");
sqlite> SELECT * FROM Table_A;
1|name_1
2|name_2
3|name_3
4|name_4
sqlite> SELECT * FROM Table_B;
1|name_5
2|name_1
3|name_6
4|name_3
sqlite> SELECT *
...> FROM Table_A
...> INNER JOIN Table_B
...> ON Table_A.name = Table_B.name;
id name id name
---------- ---------- ---------- ----------
1 name_1 2 name_1
3 name_3 4 name_3
sqlite> SELECT *
...> FROM Table_A
...> LEFT OUTER JOIN Table_B
...> ON Table_A.name = Table_B.name;
id name id name
---------- ---------- ---------- ----------
1 name_1 2 name_1
2 name_2
3 name_3 4 name_3
4 name_4
sqlite> SELECT *
...> FROM Table_B
...> LEFT OUTER JOIN Table_A
...> ON Table_A.name = Table_B.name;
id name id name
---------- ---------- ---------- ----------
1 name_5
2 name_1 1 name_1
3 name_6
4 name_3 3 name_3
sqlite> SELECT Table_A.id, Table_A.name, Table_B.id, Table_B.name
...> FROM Table_A
...> LEFT JOIN Table_B USING(name)
...> UNION ALL
...> SELECT Table_A.id, Table_A.name, Table_B.id, Table_B.name
...> FROM Table_B
...> LEFT JOIN Table_A USING(name)
...> WHERE Table_A.name IS NULL;
id name id name
---------- ---------- ---------- ----------
1 name_1 2 name_1
2 name_2
3 name_3 4 name_3
4 name_4
1 name_5
3 name_6
sqlite> SELECT *
...> FROM Table_A
...> LEFT OUTER JOIN Table_B
...> ON Table_A.name = Table_B.name
...> WHERE Table_B.id IS null;
id name id name
---------- ---------- ---------- ----------
2 name_2
4 name_4
sqlite> SELECT *
...> FROM Table_B
...> LEFT OUTER JOIN Table_A
...> ON Table_A.name = Table_B.name
...> WHERE Table_A.id IS null;
id name id name
---------- ---------- ---------- ----------
1 name_5
3 name_6
sqlite> SELECT Table_A.id, Table_A.name, Table_B.id, Table_B.name
...> FROM Table_A
...> LEFT JOIN Table_B USING(name)
...> WHERE Table_B.name IS null
...> UNION ALL
...> SELECT Table_A.id, Table_A.name, Table_B.id, Table_B.name
...> FROM Table_B
...> LEFT JOIN Table_A USING(name)
...> WHERE Table_A.name IS null;
id name id name
---------- ---------- ---------- ----------
2 name_2
4 name_4
1 name_5
3 name_6

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store