Which of the following describes the number of rows of data that can be expected after performing both joins in the order stated, considering the customer table as the main table?

A data analyst has been asked to merge the tables below, first performing an INNER JOIN and then a LEFT JOIN:

Customer Table –

In-store Transactions C

Which of the following describes the number of rows of data that can be expected after performing both joins in the order stated, considering the customer table as the main table?
A . INNER: 6 rows; LEFT: 9 rows
B . INNER: 9 rows; LEFT: 6 rows
C . INNER: 9 rows; LEFT: 15 rows
D . INNER: 15 rows; LEFT: 9 rows

View Answer

Answer: C

Explanation:

An INNER JOIN returns only the rows that match the join condition in both tables. A LEFT JOIN returns all the rows from the left table, and the matched rows from the right table, or NULL if there is no match. In this case, the customer table is the left table and the in-store transactions table is the right table. The join condition is based on the customer_id column, which is common in both tables.

To perform an INNER JOIN, we can use the following SQL query:

SELECT * FROM customer INNER JOIN in_store_transactions ON customer.customer_id = in_store_transactions.customer_id;

This query will return 9 rows of data, as shown below:

customer_id | name | lastname | gender | marital_status | transaction_id | amount | date 1 | MARC | TESCO | M | Y | 1 | 1000 | 2020-01-01 1 | MARC | TESCO | M | Y | 2 | 5000 | 2020-01-02 2 | ANNA | MARTIN | F | N | 3 | 2000 | 2020-01-03 2 | ANNA | MARTIN | F | N | 4 | 3000 | 2020-01-04 3 | EMMA | JOHNSON | F | Y | 5 | 4000 | 2020-01-05 4 | DARIO | PENTAL | M | N | 6 | 5000 | 2020-01-06 5 | ELENA | SIMSON| F| N|7|6000|2020-01-07 6|TIM|ROBITH|M|N|8|7000|2020-01-08 7|MILA|MORRIS|F|N|9|8000|2020-01-09

To perform a LEFT JOIN, we can use the following SQL query:

SELECT * FROM customer LEFT JOIN in_store_transactions ON customer.customer_id = in_store_transactions.customer_id;

This query will return 15 rows of data, as shown below:

customer_id|name|lastname|gender|marital_status|transaction_id|amount|date 1|MARC|TESCO|M|Y|1|1000|2020-01-01 1|MARC|TESCO|M|Y|2|5000|2020-01-02 2|ANNA|MARTIN|F|N|3|2000|2020-01-03 2|ANNA|MARTIN|F|N|4|3000|2020-01-04 3|EMMA|JOHNSON|F|Y|5|4000|2020-01-05 4|DARIO|PENTAL|M|N|6|5000|2020-01-06 5|ELENA|SIMSON||F||N||7||6000||2020-01-07 6||TIM||ROBITH||M||N||8||7000||2020-01-08 7||MILA||MORRIS||F||N||9||8000||2020-01-09 8||JENNY||DWARTH||F||Y||NULL||NULL||NULL

As you can see, the customers who do not have any transactions (customer_id = 8) are still included in the result, but with NULL values for the transaction_id, amount, and date columns. Therefore, the correct answer is C: INNER: 9 rows; LEFT: 15 rows.

Reference: SQL Joins – W3Schools