Question

Customer Transaction Table – CUSTOMER_TXNS

Column Name	Description	Type
Account_id	Account identifier	Integer
Txn_timestamp	Time of transaction (UTC)	Timestamp
Product_id	The id of the product purchased	Integer
Txn_Amt	The revenue amount of the transaction	Float
Txn_Qty	The number of items purchased	Integer

NOTE: Customer Transaction table has multiple records per account_id.

Customer Master Table – CUSTOMER_MSTR

Column Name	Description	Type
Account_id	Account identifier	Integer
Country	Country Code	Character(3)
Address	Address of the customer	Character(64)
Registerd_Dt	Date the account id was first used	Date
Tier	Account Tier	Integer

NOTE: Customer Master table has one record per account_id.

Table CUSTOMER_MSTR has 100 records and table CUSTOMER_TXNS has 500 records. What is the maximum number of records you can get in a result set if you did the following types of joins? • INNER JOIN on account_id • LEFT JOIN CUSTOMER_MSTR with CUSTOMER_TXNS on account_id • UNION • UNION ALL Feel free to provide a more detailed explanation with assumptions if you wish.

Answer 1

The maximum number of records in the record set on performing the following type of joins are:

• INNER JOIN on account_id

Inner join selects only records that have matching values in both tables, to get the maximum number of records lets assume that all the records in the Customer Transaction table (containing 500 records) have a account_id value that is present in the Customer Master table (containing 100 records). Therefore the maximum number of records obtained will be 500.

• LEFT JOIN CUSTOMER_MSTR with CUSTOMER_TXNS on account_id

Left join selects all the records from the left table (in this case the CUSTOMER_MSTR table containing 100 records) and the matched records from the right table (in this case the CUSTOMER_TXNS table containing 500 records). To get the maximum number of records lets assume that all the records in the CUSTOMER_TXNS table have a account_id value present in the CUSTOMER_MSTR table. Therefore the maximum number of records will be 500 as all the account id's from the customer taxation table successfully match with the customer master.

• UNION

Union operator is used to combine the result set of two or more SQL queries and display only the distinct values. But all the queries must have the same number of columns and the datatype of the columns in both queries must be similar. In this case, as the datatype of the columns in both the queries are different the query will result in a error. Therefore the maximum number of records returned will be 0.

• UNION ALL

Similar to the Union operator, union all operator can also be used to combine the result set of two or more SQL queries but it displays the duplicate values also. Similar to the Union operator all the queries must have the same number of columns and the datatype of the columns in both queries must be similar. In this case, as the datatype of the columns in both the queries are different this query will too result in a error. Therefore the maximum number of records returned will be 0.