Basic SQL JOIN

So you have some tables in SQL with a relationship between them, and you want to return something from one table, but based on a condition in another. Great! JOIN

As always, I think it is best to talk about this by looking at an example. We are going to borrow a problem close to one I found on hackerRank the other day. Basically you had a city, and a country table.

The city table does have a row countryCode, which matches up with the code row in the country table. This is a referred to as a Foreign Key when it is in the City table, and a Primary Key when it is in it's own table country.

Task : Write a query to get all cities w/ continent of 'AFR'

Step 1: SELECT statement

So given these two tables, if you've never done a join before, I want you to think about just the first part of this question.

Write a query to get all cities

Well, this first part is the same as it would be if we only had one table. So don't worry about the other table yet, lets just write the first part

SELECT city.name FROM city

Step 2. JOIN statement

We need to reference the other table Country even though we are not returning anything from it. Since our WHERE will be dependent on a row within it, we need to JOIN it to get access.

JOIN country

Done.

Step 3. ON Ok not really done... SQL is pretty great, but you still need to explain how you are joining these, so we need to explain the two keys that match up between these tables.

Write a equality statement for the two rows that match up

ON city.countryCode = country.code

Step 4. WHERE

Now that we have JOINED the country table we can use WHERE like we normally would. At this point I generally imagine that I've created a super table.

We haven't really, but we do have access to all the rows in each table. It would be really inefficient to have each city entry with all this extra country data unless we really needed it. But using a JOIN we can act like we do now. We make our selection based only on those countries within the continent Africa.

Cities have no direct connection to continents which could be it's own table here as well, but since they have a direct connection to the country, we can query WHERE our contry.continent is equal to 'Afr'.

WHERE country.continent = 'AFR'

Thats the last piece of the puzzle! So our total block of SQL query would look something like this.

SELECT city.name FROM city
JOIN country
ON city.countryCode = country.code
WHERE country.continent = 'AFR'

Alright, thats a solid basic JOIN between two different tables with a One-to-Many relationship. Go find a couple basic SQL JOIN challenges and try out your new knowledge. If you feel like you're getting the hang no problem, look for some problems with many-to-many relationships where you need to use a JOIN TABLE. You got this!

Happy coding,

James