CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
rap-2-h
rap-2-h

Reputation: 32008

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

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?

Upvotes: 6

Views: 7115

Answers (2)

dfundako
dfundako

Reputation: 8324

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)

Upvotes: 11

joop
joop

Reputation: 4513

the * notation even works for a table without any columns. ( * is not more expensive than 1; the parser understands that the results from subquery are not wanted, similar to 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
        );

Upvotes: 1

Related Questions