Difference Between INNER JOIN and OUTER JOIN.

In Relational databases, the ability to combine data from multiple tables is crucial for effective data analysis and reporting. SQL (Structured Query Language) provides several types of joins to facilitate this process, with the most commonly used being INNER JOIN and OUTER JOIN. Understanding the differences between these two types of joins is essential for anyone working with databases.

What is JOIN in SQL?

In SQL, a JOIN is a powerful operation that allows you to combine rows from two or more tables based on a related column between them. Joins are essential for querying data from multiple tables in a relational database, enabling you to retrieve meaningful information that spans across different entities.

There are several types of JOINs in SQL, each of which serves a different purpose:

INNER JOIN.
LEFT JOIN (or LEFT OUTER JOIN).
RIGHT JOIN (or RIGHT OUTER JOIN).
FULL JOIN (or FULL OUTER JOIN).
CROSS JOIN.

What is an INNER JOIN in SQL?

An INNER JOIN in SQL is a type of join that retrieves records from two or more tables where there is a match between the specified columns. It returns only the rows that have corresponding values in both tables, effectively filtering out any rows that do not meet the join condition. This makes INNER JOIN a powerful tool for combining related data from different tables in a relational database.

Syntax of INNER JOIN

The basic syntax for an INNER JOIN is as follows:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

SELECT columns: Specifies the columns you want to retrieve from the tables.
FROM table1: Indicates the first table from which to retrieve data.
INNER JOIN table2: Specifies the second table to join with the first table.
ON table1.common_column = table2.common_column: Defines the condition for the join, specifying the columns that should match between the two tables.

Example of INNER JOIN.

Customers
CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	Canada

Orders
OrderID	CustomerID	OrderDate
101	1	2023-01-15
102	2	2023-02-20
103	1	2023-03-10
104	4	2023-04-05

To retrieve a list of customers along with their orders, you can use an INNER JOIN:

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

Result:

CustomerName	OrderID	OrderDate
Alice	101	2023-01-15
Bob	102	2023-02-20
Alice	103	2023-03-10

In this example, the result set includes only the customers who have placed orders. Charlie is excluded because there are no matching records in the Orders table for CustomerID 3. Similarly, the order with CustomerID 4 is not included because there is no corresponding customer in the Customers table.

Key Points

Filtering: INNER JOIN filters out rows that do not have matching values in both tables.
Multiple Joins: You can perform multiple INNER JOINs in a single query to combine data from more than two tables.
Performance: INNER JOINs are generally efficient, especially when the join columns are indexed.

What is an OUTER JOIN in SQL?

An OUTER JOIN in SQL is a type of join that retrieves records from two or more tables, including those that do not have matching values in both tables. Unlike an INNER JOIN, which only returns rows with matching values, an OUTER JOIN includes all rows from one table and the matched rows from the other table. If there is no match, NULL values are returned for the columns of the table that do not have a corresponding match.

Types of OUTER JOIN.

There are three main types of OUTER JOINs:

1. LEFT OUTER JOIN (or LEFT JOIN): Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Example of LEFT OUTER JOIN:

Consider the same Customers and Orders tables from the previous examples:

Customers
CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	Canada

Orders
OrderID	CustomerID	OrderDate
101	1	2023-01-15
102	2	2023-02-20
103	1	2023-03-10
104	4	2023-04-05

To retrieve a list of all customers along with their orders (including customers who have not placed any orders), you can use a LEFT OUTER JOIN:

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

Result:

Customers and Orders
CustomerName	OrderID
Alice	101
Bob	102
Alice	103
Charlie	NULL

In this result, Charlie is included even though he has not placed any orders, with a NULL value in the OrderID column.

2. RIGHT OUTER JOIN (or LEFT JOIN): RIGHT OUTER JOIN (or RIGHT JOIN): Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Example:

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

3. FULL OUTER JOIN (or FULL JOIN): Returns all rows when there is a match in either the left or right table records. If there is no match, NULL values are returned for the columns of the table that do not have a match.

Example:

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