The INNER JOIN returns only if there are matching rows between the tables, whereas the OUTER JOIN returns both matched and unmatched rows with NULL values if there are no values.
It is important to distinguish between INNER JOIN and OUTER JOIN since it influences the amount of data retrieved. INNER JOIN is perfect when only the related records are required, while OUTER JOIN never loses any data, which makes it ideal for reporting and analytics. In this blog, we will be discussing INNER JOINS, OUTER JOINS and the key differences between them in detail and understand them with the help of simple examples.
Table of Contents:
What is JOIN in SQL?
The JOIN in SQL is used to combine tables based on the relation between the columns. The JOIN is used to combine common data from multiple tables into a single table.
Now, we will create a database to perform operations with the help of INNER JOIN and OUTER JOIN queries.
Creating and Inserting the 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 how the table will look after creation.
What is an INNER JOIN in SQL?
The INNER JOIN in SQL will combine matching or common data from both tables based on the conditions provided. It will not retrieve data if there are no common matches between them.
Example:
SELECT Customers.CustomerName, Orders.OrderID, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Output:
Explanation:
Here the INNER JOIN has been performed, and by that, it retrieved the customer names “Priya” and “Ramya” as their data has been present in both the tables.
What is OUTER JOIN in SQL?
The OUTER JOIN in SQL combines both matched and unmatched data along with the null values if no values are present.
There are three types of OUTER JOINs:
- Left-OUTER JOIN
- Right-OUTER JOIN
- Full OUTER JOIN
LEFT OUTER JOIN
The left-OUTER JOIN retrieves all the data from the left tables, and the matching records from the right table, even the NULL values will be returned if there is no matching data.
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 data from the order table (left), and the matching data from the product table (right), which is those who ordered products, will be fetched along with NULL values (those who didn’t purchase).
Right OUTER JOIN
The right-OUTER JOIN will return the data from the right table and the matching records from the left table, even the NULL values will be returned if there is no matching data.
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 will retrieve all the data from the product table (right), including NULL values and the matching data from the order table (left).
Full OUTER JOIN
The full OUTER JOIN retrieves all the data from both the order and product tables, including the column without 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 gets all the data from both tables (order and product), including the one who purchased the products.
Difference between INNER JOIN and OUTER JOIN in SQL
| INNER JOIN | OUTER JOIN |
| Retrieve data only if there is a match between the tables. | Retrieve all the data even if there is no match. |
| It will not return NULL values | In Full and Right OUTER JOINs, the data will be retrieved as NULL values if there is no data in any of the respective tables. |
| INNER JOIN does not have any type. | OUTER JOIN has three types Left-OUTER JOIN, Right-OUTER JOIN, and Full-OUTER JOIN. |
| The process will be faster because it only returns matching values. | The process will be slower because it needs to handle NULL values. |
Example Scenarios
Case 1: To find all orders, even if they don’t have a customer, use an OUTER JOIN. This can be solved using Right-OUTER JOIN
Example:
SELECT Customers.CustomerName, Orders.OrderID, Orders.Product
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Output:
Explanation: It prints all the values from the right table (product) and also the matching values from the left (order) table.
Case 2: Getting all the details of the purchase that has been made can be achieved using Full OUTER JOIN.
Example:
SELECT Customers.CustomerName, Orders.OrderID, Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Output:
Explanation: A full OUTER JOIN prints all the data from the right (product) and left tables (order) even if the order is not placed.
Conclusion
SQL JOINs combine rows from several tables on some common value. INNER JOIN returns only matching data from both tables, which makes it good for filtering. LEFT OUTER JOIN returns all records from the left table with unmatched values replaced with NULL. RIGHT OUTER JOIN accomplishes this but instead for the right table. FULL OUTER JOIN unites both tables with missing values replaced with NULL. Knowing these differences makes it easier to select the proper JOIN for your purposes.