I don't even know where I picked this little tip up from. I think I read an example query on someone's blog recently.

Every SQL developer at some point has had to write a query that includes the "WHERE EXISTS" clause. You're looking for rows in one table where an equivalent row exists in another table. Let's use customers and orders as an example:

SELECT * FROM Customers c WHERE EXISTS ( SELECT * FROM Orders o WHERE o.CustNo = c.CustNo)

So ... this is a pretty bad query for a lot of reasons, most notably the use of  "*" to retrieve all columns rather than just the subset of columns we're interested in. The first thing you'd probably do is change it to look something like this:

SELECT CustName FROM Customers c WHERE EXISTS ( SELECT OrderNo FROM Orders o WHERE o.CustNo = c.CustNo)

This is better, but let's take one more step. We know that all we need to look at in the "Orders" table is the CustNo ... so if we only retrieve that, the server need not look at the table itself. It can simply scan the index and get the CustNo from that. So:

SELECT CustName FROM Customers c WHERE EXISTS ( SELECT CustNo FROM Orders o WHERE o.CustNo = c.CustNo)

Now we're looking pretty good. However, there's actually an even neater improvement we can make to that subquery.

If all we care about (as in this example) is that the subquery returns any rows (ie. there is at least one order for that customer), then it doesn't matter what we return, provided something is returned. So there's nothing stopping us from simply selecting a constant value, like "1". So, it's possible to do this:

SELECT CustName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustNo = c.CustNo)

Now the subquery doesn't even need to retrieve any details from the Orders table or index at all!

If nothing else, it's less to type, and it will definitely be an improvement over selecting "*" in your subquery. Give it a whirl!