Joins in SQL can be hard to get your head round initially. What I found different compared to other programming languages was having to effectively join all tables within one statement, instead of a tool like Tableau Prep where you can join things in different steps and see your progress along the way.
Here are my key reminders/tips for joins in SQL:
- Alias your tables. Writing a short code for each table will help you remember which fields come from which tables.
An example of aliasing (inner joining tables A and B on the unique_id field and keeping all fields from both tables where there is a match):
SELECT*
FROM TABLE_A A
INNER JOIN TABLE_B B
ON A.unique_id=B.unique_id
- To select only certain fields, use this aliasing again. Using the same example as above, this looks like:
SELECT
A.field_1
,A.field_2
,B.field_1
,B.field_2
FROM TABLE_A A
INNER JOIN TABLE_B B
ON A.unique_id=B.unique_id
- Check the number of rows.
It seems obvious, but if you are unfamiliar with SQL, it’s best to frequently check how many rows your joins are resulting in, in order to know you have joined the tables correctly.
- Make sure the join is correct.
Following on a similar note from the previous point, make sure you are using the correct join type within your query. There are 7 joins in SQL; left, right, left outer, right outer, full outer, inner and full outer excluding inner. It’s important to be aware of what each join actions and that you’re using the appropriate one.