What do I have to SELECT in a WHERE EXIST clause?

Question

What do I have to SELECT in sub query of a WHERE EXIST clause?

Here is a random query with a WHERE EXIST clause:

SELECT a.*
FROM a
WHERE EXISTS
(SELECT *
 FROM b
 WHERE  b.a_id = a.id)

So, * is selected in b. But it makes no sense to me because I don't want to select anything (unless I misunderstood the behavior of WHERE EXIST). I could have selected 1 but that seems weird too.

I think it does not really matter in term of execution speed and anything could have worked, but it could matter in terms of readability and "semantics". (I'm not sure about the words I use!). Is there any best practice for this? If so, why one way is chosen over another?

Answer 1

It doesn't matter. A good practice is to use SELECT 1 to indicate it is a non-data returning subquery.

The select is not evaluated and doesn't matter. In SQL Server you can put a SELECT 1/0 in the exists subquery and it will not throw a divide by zero error even.

Related: What is easier to read in EXISTS subqueries? https://dba.stackexchange.com/questions/159413/exists-select-1-vs-exists-select-one-or-the-other

For the non-believers:

 DECLARE @table1 TABLE (id INT)
 DECLARE @table2 TABLE (id INT)

 INSERT INTO @table1
 VALUES
 (1),
 (2),
 (3),
 (4),
 (5)

 
 INSERT INTO @table2
 VALUES
 (1),
 (2),
 (3)

SELECT * 
FROM @table1 t1
WHERE EXISTS (
SELECT 1/0
FROM @table2 t2
WHERE t1.id = t2.id)