mysql

Question

The following query results in 2,698 rows of data being retrieved, but the auditor_report table only has 1,620 rows. Analyse the query and select the reason why this discrepancy occurs.

Sandhiya Priya · Answer

Question: The following query results in 2,698 rows of data being retrieved, but the auditor_report table only has 1,620 rows. Analyse the query and select the reason why this discrepancy occurs. Answer: The discrepancy occurs because the query uses a JOIN (most likely an INNER JOIN or LEFT JOIN ) that matches multiple rows from another table for each record in auditor_report . When a join condition is not unique — for example, if the joined table has duplicate matching keys — the result set will multiply rows. Each matching combination between the two tables will create a new row in the output, leading to more rows than exist in the base table. Example: SELECT a.*, b.* FROM auditor_report a JOIN auditor_details b ON a.report_id = b.report_id; If: auditor_report has 1,620 rows , auditor_details has multiple rows for each report_id , then the result may expand to 2,698 rows or more , depending on how many matches exist per report. In short: The query returns more rows than the auditor_report table because of a one-to-many relationship in the join, causing row duplication . Tip: To verify, you can run: SELECT report_id, COUNT(*) FROM auditor_details GROUP BY report_id HAVING COUNT(*) > 1; This will show which report_id s cause duplicates in the join.

mysql

Answers (1)