Subquery in where statement returns more than just null values-CodePudding

EDIT: Didn't realize the original post was in oracle however this question still remains.

I was attempting to answer another post as I thought I could answer it. Turns out I came to a solution but don't know why this works.

The question was how do you return rows where the subset_ID is the same but one record has data in the Parent columns and the other record(s) don't have data in the Parent columns? The original table looks like this (unfortunately not much more to go off of):

Parent_ID	Parent_Name	Subset_Name	Subset_ID	Address
123456	SPECIAL	special_shop	9876	1234 road st
NULL	NULL	special_shop	9876	1234 road st
654321	NOT_SPECIAL	not_special_shop	9877	1258 diff st
654321	NOT_SPECIAL	not_special_shop	9877	1258 diff st

The solution would look like below:

Parent_ID	Parent_Name	Subset_Name	Subset_ID	Address
123456	SPECIAL	special_shop	9876	1234 road st
NULL	NULL	special_shop	9876	1234 road st

The solution I came up with that gives the above result is:

SELECT *
FROM #Sometable
WHERE 
Subset_ID in (SELECT ST2.Subset_ID FROM #Sometable ST2 WHERE ST2.Parent_ID IS NULL) 
AND 
Subset_ID in (SELECT ST3.Subset_ID FROM #Sometable ST3 WHERE ST3.Parent_Name IS NULL)

Why does this work when just the subquery ran alone returns:

Parent_ID	Parent_Name	Subset_Name	Subset_ID	Address
NULL	NULL	special_shop	9876	1234 road st

Would this always work and why does it work? If not always what are the edge cases?

CodePudding user response：

The subquery is basically only used to find the relevant Subset_IDs. As your subquery results show, only one Subset_ID is relevant (9876) in the source data. When used in the WHERE clause, you're saying, "return all the records from #Sometable where the Subset_ID = 9876". In the source data, there are only 2 records with Subset_ID = 9876 and they are both returned.