Chad Callihan protects the reputation of SELECT *:
There are plenty of scenarios where using SELECT * can be an issue. Using SELECT * with EXISTS isn’t one of them.
When using EXISTS and SELECT * together, SQL Server is smart enough to realize what you’re doing and knows you don’t care about what’s in the SELECT.
Read on for an example. I’ve trained myself (been trained?) still to use
SELECT 1. The reason is, I know
SELECT * works exactly the same way but the benefit of using
SELECT 1 is that doing this consistently allows you to do a search for
SELECT * in your code base to find actual perpetrators (people writing queries expecting to return the entire result set and which may be susceptible to performance problems or future maintainability problems). Using
SELECT 1 in the
EXISTS clause means you get fewer false positives in that search as a result.
That said, Joe Celko chimes in to provide some of the history behind
SELECT * as the convention for references in the