Looking through the results of the query we wrote in the previous screen, we can see a number of different reasons that countries don’t have corresponding values in
- Countries with small populations and/or no major urban areas (which are defined as having populations of over 750,000), eg San Marino, Kosovo, and Nauru.
- City-states, such as Monaco and Singapore.
- Territories that are not themselves countries, such as Hong Kong, Gibraltar, and the Cook Islands.
- Regions & Oceans that aren’t countries, such as the European Union and the Pacific Ocean.
- Genuine cases of missing data, such as Taiwan.
It’s important whenever you use inner joins to be mindful that you might be excluding important data, especially if you are joining based on columns that aren’t linked in the database schema.
Right Joins and Outer Joins
There are two less-common join types SQLite does not support that you should be aware of. The first is a right join. A right join, as the name indicates, is exactly the opposite of a left join. While the left join includes all rows in the table before the
JOIN clause, the right join includes all rows in the new table in the
JOIN clause. We can see a right join in the Venn diagram below:
The following two queries, one using a left join and one using a right join, produce identical results.
SELECT f.name country, c.name city FROM facts f LEFT JOIN cities c ON c.facts_id = f.id LIMIT 5;
SELECT f.name country, c.name city FROM cities c RIGHT JOIN facts f ON f.id = c.facts_id LIMIT 5;
The main reason a right join would be used is when you are joining more than two tables. In these cases, using a right join is preferable because it can avoid restructuring your whole query to join one table. Outside of this, right joins are used reasonably rarely, so for simple joins it’s better to use a left join than a right as it will be easier for your query to be read and understood by others.
The other join type not supported by SQLite is a full outer join. A full outer join will include all rows from the tables on both sides of the join. We can see a full outer join in the Venn diagram below:
Like right joins, full outer joins are reasonably uncommon. The standard SQL syntax for a full outer join is:
SELECT f.name country, c.name city FROM cities c FULL OUTER JOIN facts f ON f.id = c.facts_id LIMIT 5;
facts with a full outer join, the result will be be the same as our left and right joins above, because there are no values in
cities.facts_id that don’t exist in
Let’s look at the Venn diagrams of each join type side by side, which should help you compare the differences of each of the four joins we’ve discussed so far.
Next, let’s practice using joins to answer some questions about our data.