SQLite Complex Joins
SQLite does not support Right Outer Joins or Full Outer Joins so in this article I am going to get into how to get around that as well as deep dive into all joins in sqlite.
The picture below contains eight Venn diagrams. Each represents a set of data you may want to extract from two tables or a type of join you may want to perform using SQLite to extract that data.
The first thing I’ll do in this article is explain how to create your database containing your tables and data in SQLite. If you already know how to do this, I recommend skipping to the next section, where I get into joins.
Setting Up the Data
The first thing we’ll do is create a database in which we can store our tables. We do this with the following command:
% sqlite3 example.dbThis command will create a database in the directory in which we typed that code.
The next thing we want to do is create two tables. In this example, we’ll call them Table_A and Table_B.
Creating Table A and B:
sqlite> CREATE TABLE Table_A (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Table_B (id INTEGER PRIMARY KEY, name TEXT);Then we insert rows into Table A:
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")And we insert rows in Table B:
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");We can check to see all of the rows in Table A:
sqlite> SELECT * FROM Table_A;
1|name_1
2|name_2
3|name_3
4|name_4And all of the rows in Table B:
sqlite> SELECT * FROM Table_B;
1|name_5
2|name_1
3|name_6
4|name_3As you can see from the above two pictures, name 1 and name 3 are in both tables.
If we wanted to use SQLite to see which names were in both Table A and Table B, we would perform an inner join.
Inner Join
An inner join can be represented by the Venn diagram below. The yellow area represents the data that are in both Table A and Table B. In our example, the yellow area would represent names that are in both Table A and Table B.
The left two columns in the outputted table represent Table A’s IDs and their corresponding names. The right two columns represent Table B’s IDs and their corresponding names. For each name that is in both Table A and Table B, the inner join will create a row. As you can see, where there is a name under Table A’s name column there is a matching name in Table B’s name column. To perform an inner join, see the syntax below as well as the output.
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_3Left Outer Join
Let’s say that you wanted not just the names that were in both Table A and Table B, but you also wanted all of the names from Table A. Then you would perform a left outer join. A left outer join is represented in the Venn diagram below. The highlighted intersection area is like the data that we had from our inner join. It’s the data that are in both Table A and Table B. The highlighted area in Circle A that does not intersect with Circle B is all of the data in Table A that does not have a matching value in Table B.
In our example, the left outer join will represent all of the names in Table A along with the corresponding names in Table B if there is a match. The syntax for this left outer join is in the picture below.
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_4The left two columns are Table A’s IDs and corresponding names. The right two columns are Table B’s IDs and corresponding names. As you can see, all of the data from Table A is outputted. Next to each piece of data from Table A is either data containing a matching name from Table B or empty entries. The former happens if there is a match. The latter happens if there is no match.
Right Outer Join
Now, inner joins and left outer joins are the only two joins that are supported by SQLite. However, there exist several other joins that we can emulate if we can be a little creative.
One join that you may want to perform is a right outer join, which is just like a left outer join in that you wanted not just the names that are in both Table A and Table B, but all of the names in Table B.
A right outer join can be represented in the Venn diagram below. The highlighted intersection area is like the data that we had from our inner join. It’s the data that are in both Table A and Table B. The highlighted area in Circle B that does not intersect with Circle A is all of the data in Table B that does not have a matching value in Table A.
But SQLite does not support a right outer join. Here’s where the creativity comes in. If you rotate the above Venn diagram 180 degrees, then Circle B, which was originally on the right is now on the left. This means we can just perform a left outer join.
Right outer join on Table A and B is a left outer join on Table B and A.
So what would be the syntax for performing a “left outer join” when really performing a right outer join? See below. Whereas before when we were performing a left outer join on Table A and we were selecting from Table A on that second line and left outer joining on Table B on that third line, for our fancy right outer join, we will select from Table B and left outer join on Table A.
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_3In the left two columns we have Table B’s IDs and corresponding names. On the right two columns we have Table A’s IDs and corresponding names. All of the names and IDs from Table B are there in the left two columns. If there is a matching name in Table A for each name in Table B, then that name with its ID will be represented next to that matching name. If there is no matching name, then there will be a blank entry on the right.
Full Outer Join
The next type of join that is not supported by SQLite is the full outer join. The full outer join can be represented by the Venn diagram below. Where Circle A intersects Circle B is our inner join. The highlighted area in Circle A that does not intersect with Circle B is all of the data in Table A that does not have a matching value in Table B. The highlighted area in Circle B that does not intersect with Circle A is all of the data in Table B that does not have a matching value in Table A.
The syntax for emulating a full outer join in SQLite is below. For further explanation see this SQLite tutorial on full outer joins. We have two SELECT statements brought together by a UNION ALL. The first select statement on its own would return a left outer join. The second SELECT statement on its own would return a right outer join. Without the WHERE statement at the end, we would have duplicates from the inner join area. It’s important for each select statement to have the column names organized in the same manner so that the outputted table is not convoluted.
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_6The left two columns are Table A’s IDs and corresponding names. The right two columns are Table B’s IDs and corresponding names. For each name in Table A, if there is a matching name in Table B then that matching name from Table B along with it’s corresponding ID will be displayed next to the ID and name in Table A. If there is no match, then the entries belonging to Table B will be blank or nil for that row. The same goes for the names in Table B. For each name in Table B, if there is a matching name in Table A then that matching name from Table A along with it’s corresponding ID will be displayed next to the ID and name in Table B. If there is no match, then the entries belonging to Table A will be blank or nil for that row.
Now let’s get into some joins that aren’t named. Say we wanted all of the names from Table A that aren’t in Table B. The Venn diagram below represents that situation. The highlighted area is all of the data in Table A that does not have a matching value in Table B.
The syntax to return that data is below. It’s essentially the left outer join on Table A with an extra WHERE clause that says “Only return data in which there is no matching name in Table B”.
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_4The left two columns are Table A’s IDs and corresponding names. The right two columns are Table B’s ID’s and corresponding names. As should be expected, all of Table B’s columns are blank because we intentionally returned data for names that are only in Table A and not in Table B.
It’s the same deal if we only want data that is in Table B with no matching value in Table A. It’s like a “right outer join” without the intersecting area. The Venn diagram below represents this situation.
The syntax to return this data is below. It’s just like how we emulated a right outer join using a fancy left outer join, but we’ve added a WHERE clause that tells SQLite to only return the names from Table B where there is no match between the names in Table B and the names in Table A.
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_6In this case, the left two columns represent Table B’s IDs and corresponding names. The right two columns represent Table A’s IDs and corresponding names. Just like in the previous example, the right two columns will always be blank because we queried a table that represents names that are only in Table B but not in Table A.
Lastly, we’ll talk about a situation in which you want all of the names (and IDs) from Table A and Table B where there is no match between the names in Table A and the names in Table B. This is shown in the Venn diagram below.
The syntax to emulate this type of join is below and is very similar to the syntax used to emulate a full outer join except we have a WHERE clause for each SELECT statement to eliminate any data in the intersection of Table A and Table B.
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_6The output has four rows. The left two rows are the IDs and corresponding names that belong to Table A and the right two rows are the IDs and corresponding names that belong to Table B. Since we queried a table where we have only names from both tables that only belong to one table, it makes sense that where there is data on one side there is not data on the other.
Citations
“How To Emulate SQLite FULL OUTER JOIN Clause.” SQLite Tutorial, 11 Apr. 2020, www.sqlitetutorial.net/sqlite-full-outer-join/.