if you just need to know whether something exists rather than needing the actual count, it is more efficient to use EXISTS.
OLD WAY (BAD):
SELECT *
FROM MyTable
WHERE myCondition = 1
AND (SELECT COUNT(*)
FROM myOtherTable
WHERE myFIeld = myOtherField) > 0
NEW WAY (GOOD):
SELECT *
FROM MyTable
WHERE myCondition = 1
AND EXISTS (SELECT TOP 1 *
FROM myOtherTable
WHERE myFIeld = myOtherField)
You can also use it in a Select subquery like this:
SELECT *
, CASE WHEN EXISTS (SELECT TOP 1 *
FROM mysubQTable
WHERE myField = myOtherField) THEN 1 ELSE 0 END AS myIndicator
FROM myTable
