Modules

Your first INNER JOIN:

Alright, let's break down the INNER JOIN concept with something we're all familiar with - Facebook.

Nah let's just called it Facebook! Imagine Facebook has 2 main tables: one for user profiles (users) and another for the comments users make (users_comments).

• The users table is straightforward. It has details for each user like user_id, name, and email and looks something like this:

• The users_comments table tracks all the comments made by users. It includes a comment_id, the user_id of the person who commented, and of course, the comment text:

Now, here's a common scenario: some users might be in the users table but have never made a comment. So, how do we find out who's commenting and what they're saying?

Let's use an INNER JOIN to link these two tables. This type of join will only show us the data where there's a match in both tables – basically, only the users who have made comments.

See the red circle are users that made at least 1 comment and the black circle are all users in the Facebook database. user_comments is inside the users table.

Here’s how we'd write this in SQL:

SELECT users.name AS user_name,
       users_comments.comment AS user_comment
  FROM users
  INNER JOIN users_comments
    ON users.user_id = users_comments.user_id;

The INNER JOIN is super useful for this. It ensures our results are clean and only show users who are in both tables.

Dealing with more than 2 tables:

Suppose, in addition to the users and users_comments tables, Facebook has a third table named comments_reactions, which tracks reactions (like, love, haha, etc.) to each comment.

Now, let's say we want to create a query that shows each user's name, their comment, and the reactions to that comment.

We would need to perform two INNER JOIN operations:

• one to join users with users_comments
• and another to join users_comments with comments_reactions.

SELECT 
    users.name AS username,
    users_comments.comment AS user_comment,
    comments_reactions.reaction_type AS comment_reaction
FROM users
INNER JOIN users_comments 
           ON users.user_id = users_comments.user_id
INNER JOIN comments_reactions 
           ON users_comments.comment_id = comments_reactions.comment_id;

• The first INNER JOIN connects users with users_comments on user_id
• The second INNER JOIN then connects users_comments with comments_reactions on comment_id.

This query clearly shows how to use multiple INNER JOINs to link related data across several tables in SQL!

You probably heard a lot about Joins, let's practice!