Working on some SQL today I came across a query that is using HAVING with a sub-query. I found it a bit odd as the table in the sub-query only contains one column of values and I figured it would just be simpler to join on that table.
Note the [F Types] table is a single column of unique values.
Example of what I found:
SELECT ST.COL1, ST.COL2, ST.COL3
FROM SOME_TABLE ST
HAVING ST.TYPE In (Select [Type] from [F Types]);
I was thinking that this would do the same thing and probably be better:
SELECT ST.COL1, ST.COL2, ST.COL3
FROM SOME_TABLE ST
JOIN [F Types] FT
ON ST.TYPE = FT.Types
I just wanted to make sure I was not missing something as I don't see why one would use HAVING here.
What if any is the differenct between the 2 examples?
You would use WHERE, not HAVING. HAVING is for aggregation queries. So your question is about:
SELECT ST.COL1, ST.COL2, ST.COL3
FROM SOME_TABLE ST
WHERE ST.TYPE In (SELECT f.[Type] FROM [F Types] f);
This is often preferable to the JOIN query. You do not have to worry about duplicates in [F Types] resulting in duplicate rows in the result set.
SELECT ST.COL1, ST.COL2, ST.COL3
FROM SOME_TABLE ST
WHERE EXISTS (SELECT 1 FROM [F Types] f WHERE f.[Type] = ST.TYPE);
This readily takes advantage of an index on [F Types]([Type]) and is often the fastest approach (although all three should be comparable).
All this said, nothing is wrong with the JOIN. Just under some circumstances it can produce unwanted duplicates.
Often, this is written using EXISTS rather than IN: