The INNER JOIN yields results only when there are corresponding rows in both tables, while the OUTER JOIN provides both matched and unmatched rows with NULL entries when no values are found. 

It’s crucial to recognize the distinction between INNER JOIN and OUTER JOIN as it affects the volume of data retrieved. INNER JOIN is suitable for instances when only associated records are necessary, whereas OUTER JOIN ensures that no data is omitted, making it advantageous for reporting and analysis. In this article, we’ll explore INNER JOINS, OUTER JOINS, and the significant contrasts between them with detailed explanations and straightforward examples.

Table of Contents:

• What is JOIN in SQL?
• What is an INNER JOIN in SQL?
• What is OUTER JOIN in SQL?
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
• Difference Between INNER JOIN and OUTER JOIN in SQL
• Example Scenarios

What is JOIN in SQL?

In SQL, JOIN is utilized to merge tables based on the relationship between their columns. JOIN facilitates the integration of common information from various tables into one cohesive table.

Next, we will set up a database to conduct operations using INNER JOIN and OUTER JOIN queries. 

Creating and Inserting Values into the Table:

# Create Customers Table
CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50)
);

# Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(100),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

# Insert data into Customers
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES
(1, 'Priya', 'Tamil Nadu'),
(2, 'Ramya', 'Karnataka'),
(3, 'Mani', 'Kerala'),
(4, 'Tanya', 'Maharashtra'),
(5, 'Kapoor', 'Uttar Pradesh');

# Insert data into Orders
INSERT INTO Orders (OrderID, CustomerID, Product) VALUES
(101, 1, 'Printer'),
(102, 2, 'Tablet'),
(103, 1, 'Desktop');

This is the resulting structure of the table following its creation. 

What is an INNER JOIN in SQL?

INNER JOIN in SQL unites matching or common data from both tables according to the designated criteria. If there are no matching entries, it will not provide any data.

Example:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product  
FROM Customers  
INNER JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: 

The INNER JOIN operation here retrieved the customer names “Priya” and “Ramya” since their data exists in both tables.

What is OUTER JOIN in SQL?

OUTER JOIN in SQL combines both matched and unmatched data, including NULL values where applicable.

There are three categories of OUTER JOINs:

• Left-OUTER JOIN
• Right-OUTER JOIN
• Full OUTER JOIN

LEFT OUTER JOIN 

The LEFT OUTER JOIN returns all entries from the left table, along with the corresponding records from the right table, and will include NULL values when there is no correspondence.

Example:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product  
FROM Customers  
LEFT OUTER JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: 

The LEFT OUTER JOIN retrieves all the information from the order table (left), along with matching data from the product table (right). Entries in the order table that represent purchases will be shown alongside NULL values for those who did not buy anything.

Right OUTER JOIN

The RIGHT OUTER JOIN fetches entries from the right table, along with matching records from the left table, also returning NULL values when there is no match.

Example:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product 
FROM Customers 
RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: 

The RIGHT OUTER JOIN extracts all details from the product table (right) including NULL entries and matches from the order table (left). 

Full OUTER JOIN

The FULL OUTER JOIN collects all information from both the order and product tables, including those without corresponding values. 

Example:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product  
FROM Customers  
FULL OUTER JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: 

This OUTER JOIN returns all records from both tables (order and product), including information about those who made purchases of the products. 

Difference between INNER JOIN and OUTER JOIN in SQL

INNER JOIN	OUTER JOIN
Fetch data solely if there exists a correspondence between the tables.	Fetch all the data irrespective of any correspondence.
It will not yield NULL values	In Full and Right OUTER JOINs, the data retrieved will appear as NULL values if there is no data present in any of the respective tables.
INNER JOIN lacks any specific type.	OUTER JOIN comprises three types: Left-OUTER JOIN, Right-OUTER JOIN, and Full-OUTER JOIN.
The operation will execute more swiftly as it only returns corresponding values.	The operation will execute more gradually since it must manage NULL values.

Illustrative Scenarios

Scenario 1: To retrieve all orders, even if they are not linked to a customer, employ an OUTER JOIN. Right-OUTER JOIN can be utilized to resolve this.

Sample:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product  
FROM Customers  
RIGHT OUTER JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

Result:

Clarification: This outputs all entries from the right table (product) along with the corresponding entries from the left (order) table.

Scenario 2: To acquire all the information about completed purchases, Full OUTER JOIN can be applied.

Sample:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product  
FROM Customers  
FULL OUTER JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

Result:

Clarification: A full OUTER JOIN outputs all the information from both the right (product) and left tables (order) regardless of whether an order has been made.

Summary

SQL JOINs amalgamate rows from multiple tables based on shared values. INNER JOIN yields only compatible data from both tables, making it ideal for filtration. LEFT OUTER JOIN returns every record from the left table, filling unmatched entries with NULL. RIGHT OUTER JOIN achieves this for the right table instead. FULL OUTER JOIN integrates both tables, substituting missing values with NULL. Understanding these distinctions simplifies the selection of the appropriate JOIN for your needs.

The article Difference between INNER JOIN and OUTER JOIN in SQL was first published on Intellipaat Blog.