Subquery expressions
This section introduces the subquery expressions that follow the SQL standard in AntDB. All the expressions mentioned in this section return values of Boolean type (true/false) results.
EXISTS
The argument to EXISTS is a SELECT statement or subquery in any form. We first run the subquery statement to determine if the subquery statement returns rows.
EXISTS ( subquery )
EXISTS results in "true" if the subquery returns at least one row.
If the subquery returns no rows, then the result of EXISTS is "false".
A subquery can refer to variables passed from its surrounding queries, so that it can operate like a constant when running the subquery.
We usually execute the subquery to determine if at least one row is returned, rather than all rows returned, before ending the subquery. It is not wise to write a subquery that has a negative effect (e.g., calling a sequence function), and it is difficult to tell in advance whether there is a negative effect.
Because the return result of EXISTS depends only on whether records are returned, not on the content of those records, the output list of a subquery generally makes little sense. A more general coding convention is to write all EXISTS in the form of EXISTS (SELECT 1 WHERE...). Of course, there are some exceptions to this rule, such as subqueries that use INTERSECT.
This example is similar to the inner join on column deptno, but produces at most one output row for each row of table dept, although here there are multiple matching rows of table emp.
SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);
dname
--------------
ACCOUNTING
RESEARCH
SALES
(3 rows)
Top comments (0)