Whenever I need to check for the existence of some row in a table, I tend to write always a condition like:
SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally write FROM another_table WHERE another_table.b = a_table.b )
Some other people write it like:
SELECT a, b, c FROM a_table WHERE EXISTS (SELECT 1 --- This nice '1' is what I have seen other people use FROM another_table WHERE another_table.b = a_table.b )
When the condition is
NOT EXISTS instead of
EXISTS: In some occasions, I might write it with a
LEFT JOIN and an extra condition (sometimes called an antijoin):
SELECT a, b, c FROM a_table LEFT JOIN another_table ON another_table.b = a_table.b WHERE another_table.primary_key IS NULL
I try to avoid it because I think the meaning is less clear, specially when what is your
primary_key is not that obvious, or when your primary key or your join condition is multi-column (and you can easily forget one of the columns). However, sometimes you maintain code written by somebody else... and it is just there.
Is there any difference (other than style) to use
SELECT 1instead of
Is there any corner case where it does not behave the same way?
Although what I wrote is (AFAIK) standard SQL: Is there such a difference for different databases / older versions?
Is there any advantage on explicity writing an antijoin?
Do contemporary planners/optimizers treat it differently from the