Discovering Outer Joins in SQL | Insights from a RealLife Banking Case Study

Note: All SQL queries used in the Case study is tested on Oracle,SQLServer,MySQL and PostgreSQL.

1.List all customers along with their account details and transaction details, even if they have no accounts.
SELECT c.CustomerID, c.Name, a.AccountID, a.AccountType, a.Balance,
t.TransactionID, t.TransactionType, t.Amount, t.tDate
FROM Customers c
LEFT JOIN Accounts a
ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t
ON a.AccountID = t.AccountID;
Explanation
This query returns a full list of all customers and their account and transaction details, even if the customer has no accounts or any transactions. LEFT JOIN (outer join) allows the retrieval of all customers, regardless of any matching account in the accounts table. If the customer has no accounts, then all account and transaction fields will be returned NULL. The second LEFT JOIN joins the accounts table to the transactions table so that even if there are no transactions on an account, those fields will also return NULL.
It supports MySQL, PostgreSQL, SQL Server, and Oracle.

2.Find customers who have not made any transactions in the last year.
Oracle
SELECT c.CustomerID, c.Name
FROM Customers c
LEFT JOIN Accounts a ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t ON a.AccountID = t.AccountID
AND
t.tDate >= SYSDATE - INTERVAL '1' YEAR
WHERE t.TransactionID IS NULL;
Explanation
This query finds customers who have not made any transactions in the last year. specifically using Oracle SQL syntax It's doing two LEFT JOIN operations.
The first join links Customers to Accounts this would bring all the customers even those without accounts. This second LEFT JOIN joins Accounts to the Transactions table but only for transactions which have happened within the last year t.tDate >= SYSDATE - INTERVAL '1' YEAR;.
The WHERE t.TransactionID IS NULL is the key to this question. That will clear all customers that have performed at least some transaction in the last year. This will be left with the customers either who have no transactions or who haven't made any transactions in the last year. It returns the list of inactive customers-in the case that they may have accounts-and hence can be used to find dormant accounts or customers not having recently engaged in financial activity.
__________
SQLServer
__________
SELECT c.CustomerID, c.Name
FROM Customers c
LEFT JOIN Accounts a ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t ON a.AccountID = t.AccountID
AND
t.tDate >= DATEADD(YEAR, -1, GETDATE())
WHERE t.TransactionID IS NULL;
Explanation
That SQL Server query gets the customers who made no transactions last year. It employs two LEFT JOIN statements: a first join connects the Customers table to the Accounts table, so that all customers are returned even though they may have made no accounts. The second LEFT JOIN joins Accounts table with the Transactions table but only includes transactions made inside the last year, so here t.tDate >= DATEADD(YEAR,-1,GETDATE()). In SQL Server, GETDATE() is a function that returns the current date, and the function DATEADD(YEAR,-1,GETDATE()) subtracts one year from the current date.
The WHERE t.TransactionID IS NULL condition excludes customers who have successfully created some transaction in the last year. It refers to those accounts that haven't been accessed for the past year. A query like this one is helpful in finding inactive customers because it would naturally include accounts with people who have accounts but have not made transactions within the last year. Thus, the list will show individuals with a dormant account or those who haven't recently touched the system.
__________
PostgreSQL
__________
SELECT c.CustomerID, c.Name FROM Customers c
LEFT JOIN Accounts a ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t ON a.AccountID = t.AccountID
AND t.tDate >= CURRENT_DATE - INTERVAL '1 year'
WHERE t.TransactionID IS NULL;
Explanation
This query uses LEFT JOIN to identify the customers who have not made a transaction over the past year. It starts by doing a LEFT JOIN of the Customers table and the Accounts table, including even those without accounts. It continues with another LEFT JOIN between Accounts and Transactions filtering tDate to show only transactions within the last year. t.tDate >= CURRENT_DATE - INTERVAL '1 year'. The important clause of the statement is WHERE t.TransactionID IS NULL: the link excludes all customers that have transacted with in the last year, to leave those who have not.
__________
MySQL
__________
SELECT c.CustomerID, c.Name
FROM Customers c
LEFT JOIN Accounts a ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t ON a.AccountID = t.AccountID
AND
t.tDate >= CURDATE() - INTERVAL 1 YEAR
WHERE t.TransactionID IS NULL;

3.Display accounts that do not have any associated transactions.
SELECT a.AccountID, a.AccountType, a.Balance
FROM Accounts a
LEFT JOIN
Transactions t ON a.AccountID = t.AccountID
WHERE t.TransactionID IS NULL;
Explanation
This query uses a LEFT JOIN an outer join which includes all the accounts that have no transactions associated with them. A LEFT JOIN is performed from the Transactions table using the AccountID field as the key, from the Accounts table. A LEFT JOIN assures you that all the rows in the Accounts table will be returned, even if there are no matching rows in the Transactions table.
What is the important part of the query is WHERE condition t.TransactionID IS NULL, These will only include accounts that have no corresponding transaction in the table. As the LEFT JOIN includes all accounts and fills in NULL in case there were no corresponding transactions this will be the efficient selection only for the accounts with no transactions.
This query is compatible for Oracle,MySQL,SQLServer,postgreSQL.

An outer join in SQL that illustrating how customer, account and transaction data are interconnected in a banking case study.

4.Show all transactions and their associated accounts along with customers, including accounts with no transactions.
SELECT c.CustomerID, c.Name, a.AccountID, a.AccountType, a.Balance, t.TransactionID, t.TransactionType, t.Amount, t.tDate
FROM Customers c
LEFT JOIN Accounts a
ON c.CustomerID = a.CustomerID
LEFT JOIN Transactions t
ON a.AccountID = t.AccountID;
Explanation
This query fetches all transactions, accounts and customers related to them. It subsequently fetches accounts that have no transaction. This is achieved by doing two LEFT JOINs. Start by connecting Customers to Accounts by CustomerID. This will take all customers regardless of having an account or not. This gives us a second LEFT JOIN of Accounts and connects it on column AccountID to the Transactions, so all of our accounts will be there although they don't have any transactions.
In this, it uses LEFT JOIN for both of the joins such that all accounts with no transactions and all customers with no accounts or transactions will also be returned in the result. It returns CustomerID and Name from the Customers table along with AccountID, AccountType and Balance from the Accounts table along with TransactionID, TransactionType, Amount and tDate from the Transactions table. In case of a transaction, there is no account; the fields of the corresponding transaction will then return NULL.

5.Retrieve all customers, including customers without accounts.
SELECT c.CustomerID, c.Name, c.Email, c.Phone, c.Address, a.AccountID, a.AccountType, a.Balance
FROM Customers c
LEFT JOIN Accounts a
ON c.CustomerID = a.CustomerID;
Explanation
It returns all the customers with their account information, even when they have no accounts. It applies LEFT JOIN between the Customers and Accounts table. The customers table is the left table, meaning that all customers are to appear in the result, even though a customer doesn't have any accounts in the Accounts table. For those customers who do not have any accounts, columns from the Accounts table, namely AccountID, AccountType, and Balance will return NULL. The query fetches customer details like CustomerID, Name, Email, Phone and Address, along with account details such as AccountID, AccountType, and Balance in case customers have accounts. This type of queey assists in the generation of a complete report both with and without account, hence ensuring that there are no customers that may be excluded in the results.

Key Benefits of OUTER JOIN

Comprehensive Data Retrieval
Industries often deal with large sets of data where not all of the records have matches. For example, when you are dealing with banking services, you want to view all the customers, even though some do not have accounts. This enables organizations to be more informed and avoid missing any data.

Handling Missing Data
Real world life data can incomplete . Usage of outer join is put to use by various industries in filling up the gaps and rectifying them. This may be used by a retail organization for identifying those customers who are not making purchases for a long time and further doing some marketing action against them.

Better Insights
The ability to uncover hidden relationships is crucial in many fields such as finance and healthcare. For example, a healthcare provider can analyze patients without followup appointments helping them improve patient care and retention strategies.

Flexibility in Queries
Industrially, data needs differ vastly. Outer joins are extremely versatile when it comes to queries so that various departments, such as marketing, finance, or operations can very easily derive insights related to their interests.
Simplified Reporting
Reporting to stakeholders by businesses is most likely to happen with all the information in great clarity. Outer join has been proved useful in reporting because it brings out all the relevant data elements that communicate insight better across departments and help in strategic planning.

When to Use OUTER JOIN ?

Seeing Everything from One Table
Use an outer join when you want to see all entries from one table, even if there’s no matching data in another table. For example, if you’re looking at all your customers but some don’t have accounts yet, an outer join lets you see those customers too.
Finding Missing Information
If you want to identify records that don’t have related data in another table, outer joins are your friend. Imagine checking which products haven’t been sold; an outer join will show you those products, helping you figure out why they aren’t moving.
Understanding Connections
When you need to explore how different tables relate to each other, outer joins can help you get the full picture. For instance, in a banking context, you might want to see customers and their transactions, including those who haven’t made any. This way, you can identify customers who might need a nudge to use their accounts more.
Creating Complete Reports
It enhances the performance of the query as it only returns matching records. So it would be saving some time during its execution.
Ensuring Data Integrity
When making reports that need to include as much information as possible, outer joins are very useful. For example, if you're creating a report on all employees and their projects, using an outer join ensures you include employees who might not currently be assigned to any project.

Previous Topic:-->>INNER JOIN Banking Study || Next topic:-->>Equi JOIN for Banking Case Study

Other Topics for Account Management
Cross Join Joins With Group by Having Joins with Subqueries Natural Join Self Join