Building Bridges with SQL JOINS

The JOIN operation combines rows from two or more tables based on a related column between them. This allows for the retrieval of data that spans multiple tables, enabling more complex and meaningful queries than what can be achieved with a single table.

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
Hope you had a great weekend? Can you please provide the total amount of dishes sold in the past year per country?
PS: Noticed that you’re officially a week old in the company. Hope you’re enjoying working here!
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY

There are several types of JOINs in SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN and more. In this blog post, we’ll explore the intricacies of SQL JOINs, understand their syntax, and delve into various use cases to demonstrate their utility in real-world scenarios.
The basic syntax for the JOIN operation is as follows:

SELECT columns
FROM table1
JOIN table2 ON table1.key = table2.key;

columns: are the columns we are interested in pulling from both tables.
table1 & table2: are the tables we’d like to merge together.
key: is the common column in both tables which serves the purpose of the join-er.

Common Use Cases for the JOIN operation

1. Combining Information from Multiple Tables:

Consider two tables: employees and departments. The employees table contains information about individual employees, while the departments table contains details about various departments. To retrieve a list of employees along with their respective department names, you can use an INNER JOIN:

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

This query combines data from the `employees` and `departments` tables based on the common `department_id` column, providing a comprehensive list of employees with their corresponding department names.

2. Finding Unmatched Records:

LEFT JOIN is useful when you want to retrieve all records from the left table and the matched records from the right table. This can be valuable for finding unmatched records. For example, let’s say you want to find employees who haven’t been assigned to any department:

SELECT e.employee_id, e.employee_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
WHERE d.department_id IS NULL;

This query uses a LEFT JOIN to retrieve all employees and their corresponding departments. The `WHERE` clause filters the results to include only those employees with no assigned department.

Here is a quick overview of the different type of joins and the different ways it can be applied

SQL PRACTICE

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide the total amount of dishes we sold in the past year grouped by country.

SELECT r.country, COUNT(o.dish_names) as no_of_restaurants
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id
GROUP BY country;

Note: there is an order to which SQL clauses can be called. A join comes before filtering and aggregation.

FEEDBACK FROM THE CGO

Amazing! Thanks for the swift response.
I’m currently preparing a report and I need a different cut of the same dataset. kindly provide the total amount of orders per restaurant as well.
Warm regards,
Mrs Ahmadu (CGO

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!