Database Essentials using Postgres - Writing Basic SQL Queries - Joining Tables - Outer

In this article, we will explore how to perform outer joins using SQL. Outer joins are used to retrieve records where there may not be a match in the joined table.

Key Concepts Explanation

LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all records from the left table and the matched records from the right table. If there is no match in the right table, NULL values are returned.

SELECT *
FROM left_table
LEFT OUTER JOIN right_table ON left_table.id = right_table.id;

RIGHT OUTER JOIN

The RIGHT OUTER JOIN retrieves all records from the right table and the matched records from the left table. If there is no match in the left table, NULL values are returned.

SELECT *
FROM left_table
RIGHT OUTER JOIN right_table ON left_table.id = right_table.id;

Hands-On Tasks

  1. Perform a LEFT OUTER JOIN between the orders and order_items tables.
  2. Count the number of records in the result of a LEFT OUTER JOIN between the orders and order_items tables.
  3. Perform a RIGHT OUTER JOIN between the orders and order_items tables.
  4. Count the number of records in the result of a RIGHT OUTER JOIN between the orders and order_items tables.

Conclusion

In this article, we have learned about performing outer joins in SQL using LEFT OUTER JOIN and RIGHT OUTER JOIN. These are useful when we want to retrieve records from one table even if there is no match in the other table. Practice these concepts and explore further in your SQL journey.

Watch the video tutorial here