SQL Join
Tags: sql, beginners
Published: 2020-09-23 14:49:30

# Basic SQL JOIN

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mgzs9cymwvzpt4eq3z9w.png)

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 <a href='hackerRank'>hackerRank</a> the other day. Basically you had a <em>city</em>, and a <em>country</em> table.

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

#
### 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 1](https://dev-to-uploads.s3.amazonaws.com/i/jelqu3flywe7gl30pdld.png)

### 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 col within it, we need to `JOIN` it to get access.

```
JOIN country
```
Done.
![Step 2](https://dev-to-uploads.s3.amazonaws.com/i/57zpxsm79p85iebe6bkp.png)

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 cols that match up

```
ON city.countryCode = country.code
```
![Step 3](https://dev-to-uploads.s3.amazonaws.com/i/tisn3d64qf1fh0y752y2.png)

### Step 4. WHERE
Now that we have JOINED the <em>country</em> 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 cols 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'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/3erqkala6nll6yea4irc.png)
#
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