Вивчайте RIGHT and FULL OUTER JOIN in Action | Core Join Types and Their Applications

Свайпніть щоб показати меню

When working with relational databases, you often need to retrieve information from multiple tables at once. While INNER JOIN and LEFT JOIN are commonly used, there are cases where you want to capture all records from one or both tables, regardless of whether they have matching rows in the other table. This is where RIGHT JOIN and FULL OUTER JOIN become essential.

A RIGHT JOIN returns all records from the right table and the matched records from the left table. If there is no match, the result will contain NULLs for columns from the left table. This join is appropriate when you want a complete list of records from the right table and only the related data from the left table—such as listing all orders, even if some orders are not associated with a valid customer.


              12345678
            
SELECT
    orders.order_id,
    orders.order_date,
    customers.customer_id,
    customers.name
FROM
    customers
    RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

In the example above, you see all orders, even if the customer_id in the orders table does not match any customer in the customers table. Any orders without a valid customer will show NULL values for the customer columns.

While RIGHT JOIN is useful for highlighting all records from the right table, sometimes you need to see all records from both tables, whether or not there is a match. This is where FULL OUTER JOIN comes in. It combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. If there is a match, the rows are combined; if not, the unmatched side’s columns are filled with NULLs.

A FULL OUTER JOIN is appropriate when you want a comprehensive view of all data from both tables—such as listing all customers and all orders, including customers without orders and orders without customers.


              12345678
            
SELECT
    customers.customer_id,
    customers.name,
    orders.order_id,
    orders.order_date
FROM
    customers
    FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

By using FULL OUTER JOIN, you ensure that no customer or order is left out of your results, regardless of whether they are linked in the database. This is particularly valuable for reporting and data analysis scenarios where you want to identify unmatched records or get a complete picture of your data.

The CASE statement is not limited to the SELECT clause — it can also be used directly inside ORDER BY to sort rows by a custom condition rather than a specific column. A common pattern is assigning numeric values to control sort priority:


              123456789101112131415
            
SELECT
    customers.customer_id,
    customers.name,
    orders.order_id,
    CASE
        WHEN orders.order_id IS NULL THEN 'No orders'
        ELSE 'Has orders'
    END AS order_status
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY
    CASE
        WHEN orders.order_id IS NULL THEN 0
        ELSE 1
    END;

Here, THEN 0 and ELSE 1 are not column values – they are just numbers used to define sort order. Rows where order_id is NULL get 0 and appear first; all other rows get 1 and follow.

1. What is the main difference between LEFT JOIN and RIGHT JOIN?

2. When would you use FULL OUTER JOIN instead of INNER JOIN?

3. Fill in the blanks to create a FULL OUTER JOIN query that lists all customers and their orders, including unmatched records from both tables:

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 1. Розділ 5

Запитати АІ

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

Секція 1. Розділ 5