Joining Two SQL Tables
Databases
17/05/2021
The JOIN statement allows you to combine the data from 2 or more tables. Imagine you've got two tables:
A Pets table, and
| Id | Age | Name |
|---|---|---|
| 1 | 12 | Sandy |
| 2 | 7 | Odie |
An Owners table.
| Id | Name | Pet_Id |
|---|---|---|
| 1 | Dilshan | 2 |
| 2 | John | NULL |
Suddenly, you got the brilliant 🤓 idea to combine both tables to view pet and owner data in a single place. So you execute the following command in your query window:
SELECT * FROM PetsJOIN OwnersON Pets.Id=Owners.Pet_Id;This outputs:
| Id | Age | Name | Id | Name | Pet_Id |
|---|---|---|---|---|---|
| 2 | 7 | Odie | 1 | Dilshan | 2 |
What happened?
This is the way I like to go about JOIN statements:
- Decide on a "base" table -
SELECT * FROM Pets. For instance, you may think to yourself: "Who are the owners of these pets?". - Add the data from your "target" table -
JOIN Owners. - Find the column that links both tables -
ON Pets.Id=Owners.Pet_Id. This is often an id (by design) and in this case as well (Pet id).
By default, a JOIN statement defaults to an INNER JOIN statement. This means only rows that have matching Pet Id values in both tables will be returned. Notice how pets that do not have an owner (😭) and owners that do not have a pet weren't included.
SQL Joins can grow in complexity depending on what data you're looking for and the type of JOIN statement you're using. Concerning the latter, I plan on writing a blog post soon.
