Question 269240 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does the column return matter in a WHERE EXISTS subquery?

In this SQL EXISTS operator tutorial has the following syntax for

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Does the column_name in the select statement of the subquery matter? From other answers it seems like 0, 1, *, some arbitrary column name(s) have been used.

Tags:

3

Answers


  1. 0

    It does not matter.

    If you look at the source, you will see that PostgreSQL removes the SELECT list in such subqueries. A short, standard conforming way is to use a constant like

    EXISTS (SELECT 42 FROM ...)

    If I don’t have to write standard conforming code, I prefer the even shorter

    EXISTS (SELECT FROM ...)
    Login or Signup to reply.
  2. 0

    Not only does it not matter, since version 9.4 it doesn’t even have to be there:

    Allow SELECT to have an empty target list (Tom Lane)

    This was added so that views that select from a table with zero columns can be dumped and restored correctly.

    Demo:

    create table table_name(column_name text, condition boolean);
insert into table_name values ('hello',true);

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT FROM table_name WHERE condition);
    column_name
    hello
    Login or Signup to reply.
  3. 0

    EXISTS checks for the existence of rows in the subquery, not columns in it. Rows with anything will work. Even empty rows with no columns.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search