Jose Rojas Jose Rojas - 3 months ago 8
SQL Question

SELECT with 2 subqueries issue

I'm using Oracle, and I have next query:

SELECT count(*) FROM table1;


which throws me as result
21932
, then I set previous query as subquery the next way:

SELECT count(*) FROM table2 WHERE attr1 IN (SELECT attr1 FROM table1);


this throws me as result
10489
. So I want to delete rest of rows that are not within this set, before running
DELETE
sentence I wanted to check that number of rows was the right, with the next query:

SELECT count(*) FROM table1 WHERE attr1 NOT IN
(SELECT attr1 FROM table2 WHERE attr1 IN (SELECT attr1 FROM table1));


the number of rows should be
11443 (total - 10489)
, but the statement throws me
3743
rows, Why is this the result? How could I get the number of rows I want to delete?.

Thanks in advance.

Answer

You are counting records in table2 the attr1 of which also exists in table1. That doesn't tell us anything about the records in table2 that have no such match. Could be 1 record, could be a million records all with different attr1, could be a million records all with the same attr1, etc. So there is no math to tell us what your last query should result in.

... Or there is something about the two tables and attr1 that you haven't told us.

Comments