Category: databases

SQL Joins Tutorial: Working with Databases

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…

lastmileearth January 18, 2021

Forum Description

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.

Last Mile Source

Report

There was a problem reporting this post.

Harassment Harassment or bullying behavior

Inappropriate Contains mature or sensitive content

Misinformation Contains misleading or false information

Offensive Contains abusive or derogatory content

Suspicious Contains spam, fake content or potential malware

Other

Block Member?

Please confirm you want to block this member.

You will no longer be able to:

See blocked member's posts
Mention this member in posts
Invite this member to groups
Message this member
Add this member as a connection

Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.

Report

You have already reported this .

Clear Clear All