Вивчайте 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.

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

Запитати АІ

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

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