0

Two tables in myschema: post and comment. comment's onId refers to post's is. Among the following two pieces of postgres codes, why select null in the first piece of codes? what are the differences?

DELETE FROM myschema.comment comment
  WHERE NOT EXISTS (
    SELECT NULL
    FROM myschema.post post
    WHERE post.id = comment."onId"
  );

DELETE FROM myschema.comment comment
  WHERE NOT EXISTS (
    SELECT *
    FROM myschema.post post
    WHERE post.id = comment."onId"
  );

Can anyone give some examples to show the differences? Thanks

(postgres is not same with mysql. so my question is not duplicated. I can not tag my question with mysql, and I always will not search questions tagged with mysql. That is why I put postgres in my post title.)

BAE
  • 8,550
  • 22
  • 88
  • 171

1 Answers1

0

Postgres is smart enough to ignore the select statement for exists subqueries, so it doesn't make any difference whether you select null, 1, or *.

See Subquery using Exists 1 or Exists *. That question is related to sql-server but the same applies to postgres

FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • postgres is not same with mysql. so my question is not duplicated. I can not tag my question with `mysql`, and I always will not search questions tagged with `mysql`. That is why I put `postgres` in my post title. – BAE Oct 02 '17 at 19:25
  • It does not matter much about the dbms vendor here. The standard behaviour for [NOT] EXISTS subqueries is that the select clause isn't important and really only used in these c ircumstances because the SQL standard requires a select clause. Internally these predicates are treated as semi-joins and the from and where clauses of those subqueries are the important parts of them. Select *, select 1, select null, are covention preferences. – Paul Maxwell Oct 02 '17 at 22:55