How does FULL JOIN work?

FULL JOIN is a type of JOIN that returns all rows from both tables. If the tables have a match, the rows are combined into a single result row. If there is no match, the missing values are filled with NULL. In other words, FULL JOIN combines the behavior of LEFT JOIN and RIGHT JOIN at the same time.

Let's walk through an example

We have two tables: users and subscriptions. Our goal is to get all users and all subscriptions, even if some users have no subscription, or if there is a subscription that is not linked to any user.

-- users

id | name

------------

1 | Alice

2 | Bob

3 | John

-- subscriptions

id | user_id | plan_name

-------------------------

1 | 1 | Basic

2 | 2 | Premium

3 | 5 | Enterprise

The query looks like this. Let's break it down in more detail:

-- Вибираємо стовпці, які хочемо бачити в результаті:

-- name - з таблиці users

-- plan_name - з таблиці subscriptions

SELECT

users.name,

subscriptions.plan_name

FROM

users

-- Робимо FULL JOIN таблиці subscriptions

-- по полю users.id та subscriptions.user_id

FULL JOIN

subscriptions

users.id = subscriptions.user_id;

After running the query, we get the following result:

name | plan_name

-------------------------

Alice | Basic

Bob | Premium

John | NULL

NULL | Enterprise

Key takeaways

FULL JOIN returns all records from both tables. If there is a match, the data is combined. If there is no match, one side will contain NULL.

Let's walk through an example

Key takeaways

Related Questions

Comments (0)