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?

Thank you! It does not answer the "why this way over another?" though. Let's say "why not *"? — rap-2-h, Mar 30 '18 at 15:02
It doesn't really matter in modern databases since you only test for existence. 20 years ago Oracle would create more efficient execution plans if you used `SELECT 1` while SQL Server would realize there was no need to *select* any columns and create the same execution plan. — Panagiotis Kanavos, Mar 30 '18 at 15:03
@rap-2-h the answer in the last 15 years is "it doesn't matter unless you detect a difference. If you do, it's a bug" — Panagiotis Kanavos, Mar 30 '18 at 15:04
This is answered by so many other questions and articles that I can't help but wonder if you made ***any*** effort to find the answer yourself? — MatBailie, Mar 30 '18 at 15:55
In Postgres you can leave it out completely. A SELECT without anything in the select list is valid — , Jul 30 '20 at 13:08

dfundako · Accepted Answer · 2020-07-30T13:05:45.287

10

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)

edited Jul 30 '20 at 13:05

answered Mar 30 '18 at 15:02

dfundako

8,022
3
18
34

Thanks +1! "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. " -> Fun fact! I just tried this in postgreSQL and it works too! – rap-2-h Mar 30 '18 at 15:04
3

@rap-2-h The 1/0 is fun to do and it really freaks people out during QA/Code Review. Watch them waste hours trying to understand why it doesnt error. – dfundako Mar 30 '18 at 15:05
@dfundako if they freak out, it's time to send them to a SQL performance course – Panagiotis Kanavos Mar 30 '18 at 15:06
I just tried both `select 1/0` and `select 1/0 from FooTable` in SSMS 17.6. Both queries resulted in `Divide by zero error encountered.` – Mar 30 '18 at 15:13
2

@Hosch250 The select within the EXISTS does not evaluate. The select outside does. – dfundako Mar 30 '18 at 15:18
1

`uses less bytes to the engine since 1 is a bit and * is not.` is complete nonsense. What do you mean? – joop Mar 30 '18 at 15:40
@joop Nothing. I said I heard it and didn't expand on it. I have nothing to back it up. – dfundako Mar 30 '18 at 16:25
@dfundako SELECT 1/0 is cute but I hope you're not *really* making people waste time on that, at least not in a professional environment. – 404 Mar 30 '18 at 21:34
1

@404: In Postgres you can make them waste even more time by using an empty select list `exist (select from ...)` ;) – Jul 30 '20 at 13:55

score 1 · Answer 2 · answered Mar 30 '18 at 15:36

1

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
        );

answered Mar 30 '18 at 15:36

joop

4,330
1
15
26

how do you get a row from a table without any columns? – Malachi Mar 30 '18 at 16:27
There still is a row, there just are no columns. `*` *expands* to nothing (the empty set of all columns, but still a valid set) and `EXISTS(...)` yields True, because something does exist; something without any columns... – joop Mar 30 '18 at 16:39
seems rather illogical, that is an interesting situation, there is data where data should not exist, but does exist. very interesting – Malachi Mar 30 '18 at 16:51
It is in fact very logical. A table without any rows is still a table; why shouldn't a row without any columns still be a row? Also: it makes the `ALTER TABLE` DDL more regular: you can remove a column, even if it is the only column. – joop Mar 30 '18 at 17:17
1

No need to drop the column afterwards: `create table none(); insert into none default values;` will work just as well – Jul 30 '20 at 13:56

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

2 Answers2