What do I need for SELECT in a WHERE EXIST clause?

Question

What do I need for SELECTin a subquery WHERE EXIST?

Here is a random request sentence WHERE EXIST:

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

So, *selected in b. But this makes no sense to me, because I do not want to choose anything (if I did not understand the behavior WHERE EXIST). I could choose 1, but that also seems strange.

I think that it doesn’t really matter in terms of speed of execution, and it could work, 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 is one method chosen over another?

Answer 1

It does not matter. It is good practice to use SELECT 1to indicate that it is a subquery that does not return data. I also heard that the transmission 1uses fewer bytes for the engine, since 1- bit, but *- no.

The choice is not evaluated and does not matter. In SQL Server you can put SELECT 1/0in a subquery exists and it will not throw division by zero error.

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 unbelievers:

 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)

Answer 2

* - . (* , 1, , , COUNT(*))

---

CREATE TABLE none( none INTEGER ); -- ONE column
INSERT INTO none(none) SELECT 1 ; -- ONE row

ALTER  TABLE none
        DROP COLUMN none; -- NO columns!!!!

SELECT * FROM none; -- Still one row ...
SELECT COUNT(*) FROM none; -- Still one row ...

SELECT 'Yes'
WHERE EXISTS (
        SELECT *  -- even works for NO columns ...
        from none
        );