Using cross join in a flexible banking system

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

1. Retrieve a list of all possible combinations of customers and accounts using cross join.
SELECT c.CustomerID, c.Name, a.AccountID, a.AccountType, a.Balance FROM Customers c CROSS JOIN Accounts a;
Explanation
This yields a cartesian product of all the rows of the Customers and Accounts table applying a cross join.
This will match each record in the Customers table with each record in the Accounts table based on no relationship they may have within the database. It will retrieve fields CustomerID and Name from the Customers table and fields AccountID, Account Type and Balance from the Accounts table.
All possible combinations between two tables that is if there are n rows for the customer and m rows in accounts the result set will have n*m rows. This kind of join is helpful if, for instance, one wishes to scan through all possible relationships between two datasets though it returns an enormously large result set for a table with thousands of rows.

2. Show all possible combinations of customers and transactions type ‘Deposit’ Using cross join.
SELECT c.CustomerID, c.Name, t.TransactionID, t.TransactionType, t.Amount
FROM Customers c CROSS JOIN Transactions t WHERE t.TransactionType = 'Deposit'; Explanation
In this query example CROSS JOIN has been used to show all the possible combinations of customers and transactions where TransactionType is 'Deposit'. CROSS JOIN makes every row of the Customers table cross join with every row of the Transactions table. This means a full cross product of customer and transaction records. Now, WHERE clause has been applied to the above join result to get only 'Deposit' transactions into the result. This implies that for each customer you will have all possible combinations with deposit type transactions. Even when a customer has done no deposit, he may appear with deposit transactions of other customers, due to the nature of the CROSS JOIN.

3. List all combinations of ‘Saving’ account types with ‘Transfer’ transaction types.Use Cross Join.
SELECT a.AccountID, a.AccountType, t.TransactionID, t.TransactionType, t.Amount
FROM Accounts a CROSS JOIN Transactions t
WHERE a.AccountType = 'Savings' AND t.TransactionType = 'Transfer';
Explanation
This query uses a cross join to return all possible pairs of accounts whose type is 'Savings' and pairs of transactions whose type is 'Transfer'. In doing so it combines each row from Accounts with each row in Transactions. It returns every possible pair of accounts and transactions. The WHERE clause limits the result set to only those type 'Savings' accounts and type 'Transfer' transactions.It is the query that brings back this long list of all permutations of linking savings accounts with transfer transactions not matter that there may not actually be any real relation between these objects. Even if a particular savings account has never conducted a 'transfer' transaction it will still be linked to all 'transfer' transactions in the returned list.