SQL
SELECT column1, column2, …
FROM table1
LEFT/RIGHT/FULL Outer Join table2
ON table1.column_name = table2.column_name;
- LEFT/RIGHT/FULL: Specifies the type of Outer Join (left, right, or full).
- table1, table2: Names of the tables to be joined.
- ON: Establishes the join condition, defining how rows from both tables are matched.
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:
SQL
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:
SQL
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:
SQL
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.
Mastering the Skill of Selecting the Correct Outer Join
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:
SQL
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:
SQL
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.
