Modules

Outer joins are a way to combine data from two tables while including unmatched values from one or both tables. There are three types of outer joins:

• LEFT JOIN: This type returns unmatched rows from the left table and matched rows from both tables.

• RIGHT JOIN: This type returns unmatched rows from the right table and matched rows from both tables.

• FULL OUTER JOIN: This type returns unmatched rows from both tables, as well as matched rows from both tables.

It's always better to visualize it:

How is OUTER JOIN different from INNER JOIN:

When performing an inner join, only rows that have matching values in both tables are returned. In contrast, an outer join can return rows with unmatched values in one or both tables.

Real Example from Tiktok Dataset:

Imagine you have 2 tables related to TikTok videos:

TikTok Videos Table:

This table contains information about various TikTok videos. Each row represents a video, and there is a unique identifier called "video_id" for each video. The table includes fields such as "likes," "shares," and "views."

TikTok Comments Table:

This table lists comments on TikTok videos, with one row per comment. The "video_id" field in this table corresponds to the "video_id" field in the TikTok Videos Table.

When you use an outer join between these two tables:

• A LEFT JOIN will return all TikTok videos, including those without any comments, along with the videos that have comments.
• A RIGHT JOIN will return all TikTok comments, including those not associated with any video, along with the comments related to videos.
• A FULL OUTER JOIN will return all TikTok videos and all comments, including those without any matching videos.

So if you basically want to return all Tiktok video and Tiktok comments (you can comment on a story for example), you would use a FULL OUTER JOIN and the syntax is the same as other join types:

SELECT *
FROM tiktok_videos v
FULL OUTER JOIN tiktok_comments c on v.user_id = c.user_id

In this query, I am returning everything, including those with no video_id!

Now let's see some use case for this concept!