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 cities:

  • 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:

Venn diagram of a right join

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:

Venn diagram of a full outer join

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;

When joining cities and 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 facts.id.

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.

Join Venn Diagram

Next, let’s practice using joins to answer some questions about our data.

%post_title%

Cryptovixens Source

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on pinterest
Pinterest

Responses

Your email address will not be published. Required fields are marked *

Get 20% Discount

Sign up to receive updates, promotions, and sneak peaks of upcoming products. Plus 20% off your next order.

Promotion nulla vitae elit libero a pharetra augue

Nullam quis risus eget urna mollis ornare vel eu leo. Aenean lacinia bibendum nulla sed