Modules

Okay, so we're going to explore the LEFT JOIN in SQL, are you excited? BECAUSE I AM!

We will use your favorite example : Facebook - to make it super clear. And the tables users and user_comments.

On our previous lessons, we did an INNER JOIN to find the username that made at least 1 comment. But we are not all making comments, there are some users that never made any comments on Facebook in their life. Yes they exist!

Yes, I am serious! Let's say you're being asked: What % of all FB users made at least 1 comment? How would you find that out?

This is why we gonna learn about LEFT JOIN! Let me show you the query first and then let's break that down:

SELECT 
  COUNT(DISTINCT users_comments.user_id) AS users_made_comment,
  COUNT(DISTINCT users.user_id) AS all_users
FROM users
LEFT JOIN user_comments ON users.user_id = user_comments.user_id;

Let me explain! You start with your main table (in this case, all the Facebook users), and then you attach information from the second table (the user_comments) to it. The first table is your main one and the second table only matches the users from the first table.

The black circle is your main table and the red is your user_comments.

Now, if you wanna get what % of users that made at least 1 comment, you will simply divide these 2 columns in your SQL

SELECT 
  1.00*COUNT(DISTINCT users_comments.user_id)/COUNT(DISTINCT users.user_id) 
  AS percentage_that_commented
FROM users
LEFT JOIN user_comments ON users.user_id = user_comments.user_id;

This is such an important concept in SQL, and you will use that a lot in your job as a Data Analyst or even doing interview questions.

So let's get prepared for it and practice!