0

2 Oracle SQL queries return different number of rows when number should be the same. At least I believe the number of rows should be the same.

Please see SQL query #1:

select count(*) from temp_dormer_mro
where catalog_number in (select catalog_number from temp_dormer_data);

It returns 961 rows.

Please see SQL query #2:

select count(*) from temp_dormer_data
where catalog_number in (select catalog_number from temp_dormer_mro);

It returns 1278 rows.

Please help me to understand why I got different number of rows for these 2 queries.

---------- Update #1 ------------

Data in tables looks like this: Data in tables

There is no NULL values in any row.

Table temp_dormer_data has 1,278 records. So basically second SQL query just select all rows in that table.

---------- Update #2 ------------

select count(*) from temp_dormer_mro
join temp_dormer_data
    on temp_dormer_data.catalog_number = temp_dormer_mro.catalog_number
;

select count(*) from temp_dormer_data
join temp_dormer_mro
    on temp_dormer_mro.catalog_number = temp_dormer_data.catalog_number
;

Both above SQL queries return 1,278 rows. And that number seems to be correct. But why the first SQL query returns incorrect number?

4
  • Table temp_dormer_data has 1,278 records. ... and the table temp_dormer_mro contains ___ records? why I got different number of rows for these 2 queries Because they realizes different logic.
    – Akina
    May 17, 2021 at 9:41
  • prediction: temp_dormer_data contains duplicates by catalog_number
    – Akina
    May 17, 2021 at 9:44
  • @Akina you are right, there are duplicates. May 17, 2021 at 9:59
  • If so then I don't understand why you're surprized. WHERE 1 IN (1, 1, 1) produces one "true", not three.
    – Akina
    May 17, 2021 at 10:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.