Hero Vired Logo
Programs
BlogsReviews

More

Vired Library

Complimentary 4-week Gen AI Course with Select Programs.

Request a callback

or Chat with us on

Home
Blogs
Outer Join in SQL – Explained with Examples

In SQL, when we talk about joining tables, it’s like merging information from different sources based on a common link, usually a shared column. One type of join, called Full Outer Join, or just Outer Join, grabs all the records from both tables, whether they match or not. Imagine you have a list of customers and another list of orders.

 

With a Outer Join, you’d get all customers, even those without orders, and all orders, neatly matched with their respective customers. It’s like bringing together two puzzle pieces, making sure nothing gets left out. But be cautious, with all that data, things can get overwhelming. So, think twice before using it; sometimes, a simpler approach might just do the trick.

 

In This Article

 

 

The Three Faces of Outer Joins

 

Three primary forms of Outer Join in SQL  exist, each designed for particular situations:

 

  • Left Outer Join: In a Left Outer Join, the “left” table is given priority, and all of its rows are guaranteed to be part of the results. Null values are placed in the corresponding columns from the right table for rows in the left table that do not have a match.

 

  • Right Outer Join: It is just like the left Outer Join, but it gives priority to the “right” table to ensure all of its rows are included in the result. Null values are used to fill in unmatched rows from the right table in the columns of the left table.

 

  • Full Outer Join: This type of join includes all rows from both tables, irrespective of any matches. Empty spaces in columns from one table are replaced with null values if there are no corresponding values in the other table.

 

Comprehending the Syntax

 

The format for Outer Joins is similar to what we are used to but with a small variation.

 

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. 

 

 

 

FAQ's

  • Left Outer Join: Prioritises the "left" table and includes all its rows, filling unmatched entries from the right table with null values.
 
  • Right Outer Join: Prioritises the "right" table and includes all its rows, filling unmatched entries from the left table with null values.
 
  • Full Outer Join: Includes all rows from both tables, filling unmatched entries in either table with null values.
 
SELECT column1, column2, ... FROM table1 LEFT/RIGHT/FULL Outer Join table2 ON table1.column_name = table2.column_name;
Yes, you can use Outer Joins in combination to merge data from multiple tables.

High-growth programs

Choose the relevant program for yourself and kickstart your career

You may also like

Carefully gathered content to add value to and expand your knowledge horizons

Hero Vired logo
Hero Vired is a premium LearnTech company offering industry-relevant programs in partnership with world-class institutions to create the change-makers of tomorrow. Part of the rich legacy of the Hero Group, we aim to transform the skilling landscape in India by creating programs delivered by leading industry practitioners that help professionals and students enhance their skills and employability.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance

Integrated Program in Finance and Financial Technologies

Certificate Program in Financial Analysis, Valuation and Risk Management

Management

Certificate Program in Strategic Management and Business Essentials

Executive Program in Product Management

Certificate Program in Product Management

Certificate Program in Technology-enabled Sales

Future Tech

Certificate Program in Gaming & Esports

Certificate Program in Extended Reality (VR+AR)

Professional Diploma in UX Design

Blogs
Reviews
In the News
About Us
Contact us
Vired Library
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

© 2024 Hero Vired. All rights reserved