What do you put in a subquery's Select part when it's preceded by Exists?

What do you put in a subquery's Select part when it's preceded by Exists?

Select *  
  From some_table  
 Where Exists (Select 1  
                From some_other_table  
               Where some_condition )

I usually use 1, I used to put * but realized it could add some useless overhead. What do you put? is there a more efficient way than putting 1 or any other dummy 开发者_开发问答value?

---

I think the efficiency depends on your platform. In Oracle, SELECT * and SELECT 1 within an EXISTS clause generate identical explain plans, with identical memory costs. There is no difference. However, other platforms may vary.

As a matter of personal preference, I use

  SELECT *

Because SELECTing a specific field could mislead a reader into thinking that I care about that specific field, and it also lets me copy / paste that subquery out and run it unmodified, to look at the output.

However, an EXISTS clause in a SQL statement is a bit of a code smell, IMO. There are times when they are the best and clearest way to get what you want, but they can almost always be expressed as a join, which will be a lot easier for the database engine to optimize.

SELECT *
FROM SOME_TABLE ST
WHERE EXISTS(
  SELECT 1
  FROM SOME_OTHER_TABLE SOT
  WHERE SOT.KEY_VALUE1 = ST.KEY_VALUE1
    AND SOT.KEY_VALUE2 = ST.KEY_VALUE2
  ) 

Is logically identical to:

SELECT *
FROM
  SOME_TABLE ST
  INNER JOIN
  SOME_OTHER_TABLE SOT
  ON ST.KEY_VALUE1 = SOT.KEY_VALUE1
    AND ST.KEY_VALUE2 = SOT.KEY_VALUE2

---

I also use 1. I've seen some devs who use null. I think 1 is efficient compared to selecting from any field as the query won't have to get the actual value from the physical loc when it executes the select clause of the subquery.

---

Use:

WHERE EXISTS (SELECT NULL
                FROM some_other_table  
               WHERE ... )

EXISTS returns true if one or more of the specified criteria match - it doesn't matter if columns are actually returned in the SELECT clause. NULL just makes it explicit that there isn't a comparison while 1/etc could be a valid value previously used in an IN clause.