Outer Join in SQL – Explained with Examples

Time to programme! Exploring Outer Joins with ExamplesLet’s Start Coding! Revealing Outer Joins with Illustrations

Example 1: Left Outer Join – Giving Priority to Customers and Their Orders

Picture two tables: “Customers”, containing customer data, and “Orders,” including order specifics. You want to view all customers, including those who have not made any orders yet. Here is how utilising a left Outer Join can provide assistance:

SELECT c.customer_name, o.order_id, o.order_date

FROM Customers c

LEFT Outer Join Orders o ON c.customer_id = o.customer_id;

This question gives preference to the “Customers” table (left table). Every customer, including those without a corresponding order, will be accounted for, resulting in null values for order_id and order_date.

Example 2: Concentrating on Products and Reviews (Even Without Reviews) with Right Outer Join

Say you possess a table named “Products” and another one named “Reviews.” You wish to view all products, including those that have not yet been reviewed. This is when the right Outer Join comes in handy:

SELECT p.product_name, r.review_text, r.review_rating

FROM Products p

RIGHT Outer Join Reviews r ON p.product_id = r.product_id;

This question gives higher importance to the “Products” table (the table on the right). Every product will be listed, even those without reviews, which will have empty values for review_text and review_rating.

Example 3: Full Outer Join – A Comprehensive Overview of Employees and Projects, Including Unassigned Employees

Assume you possess an “Employees” data table and a “Projects” data table with employee IDs allocated to projects. You wish to view a list of all employees and the projects they are currently working on, regardless of whether some employees are not currently assigned to any projects. Here is a complete Outer Join for an inclusive perspective:

SELECT e.employee_name, p.project_name

FROM Employees e

FULL Outer Join Projects p ON e.employee_id = p.assigned_employee_id;

This request retrieves all employees and projects, displaying empty project names for unassigned employees and empty employee names for projects without assigned employees.

The decision to use an Outer Join in SQL is based on your individual requirements. Left Outer Joins are perfect for when you need to give importance to data from a specific table and manage missing data by using null values. Right Outer Joins are useful in Excel when the attention is directed towards the second table. Full Outer Joins give a complete overview of both tables, including rows that do not have a match on either side.

Advanced Uses: Going Further than the Fundamentals

Outer Joins provide benefits beyond just including rows that do not have a match. Below are a few high-level applications:

• Discovering Abandoned Records: Utilise Outer Joins to locate rows in one table that do not have matching entries in another. This could assist in cleaning data or detecting possible errors.

Example 4: Left Outer Join – Identifying Customers Who Are Not Active.

Picture having a “Customers” table and a “Logins” table monitoring customer interaction. You need to find customers who have not logged in recently and are inactive. Here is the method:

SELECT c.customer_name, l.last_login_date

FROM Customers c

LEFT Outer Join Logins l ON c.customer_id = l.customer_id;

This inquiry gives preference to customers. Customers who have not logged in will have a blank last_login_date value, making it easy to identify inactive accounts.

• Combining Data from Multiple Tables: Utilising Outer Joins enables the merging of data from multiple tables, leading to more comprehensive datasets for analysis.

Example 5: Analysing sales of books across different genres using a Full Outer Join.

Assume you possess tables for “Books,” “Genres,” and “Sales.” You wish to view every book, including their genres (if applicable), and their overall sales numbers (even if a book has not been sold yet). Below is a complete Outer Join that generates a thorough perspective:

SELECT b.book_title, g.genre_name, SUM(s.sales_amount) AS total_sales

FROM Books b

FULL Outer Join Genres g ON b.genre_id = g.genre_id

LEFT Outer Join Sales s ON b.book_id = s.book_id

GROUP BY b.book_title, g.genre_name;

This search employs both full outer and left Outer Joins in combination. Every book is accounted for, with genre_name being displayed as “NULL” for books that do not have a specific genre assigned. Utilising the SUM aggregate function with a left Outer Join on the “Sales” table offers the total sales figures for every book, even those with zero sales.

Embrace the Strength of Outer Joins: Beneficial for Data Exploration

Outer Join in SQL is a useful resource in the SQL toolbox. Do you wish to know more about SQL? Join Hero Vired’s Accelerator programme in Business Analytics and Data Science. The programme offers a comprehensive curriculum that dives deep into the fundamentals of data analysis, specifically focusing on SQL.  The programme starts with the very basics of SQL, ensuring a strong foundation  before progressively building your skills through various modules titled “Data Analysis Using SQL.”  These modules will equip you with the ability to handle complex queries involving joins, aggregations, and data manipulation.

By enrolling in this programme, you’ll gain the expertise to confidently navigate the world of SQL, a crucial skill for any aspiring data analyst.