DB2 - SQL Exists Operator

The EXISTS operator tests for the existence of certain rows in a subquery.

The EXISTS operator returns true if the subquery returns one or more records.

The result of the EXISTS operator.

1. Is true only if the number of rows that is specified by the subquery is not zero.

2. Is false only if the number of rows specified by the subquery is zero.

3. Cannot be unknown.

Syntax:

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

Let's look at the "Orders" table data below:

Let's look at the "Customers" table data below:

Example: 1

The following SQL statement returns TRUE and lists the customers who made a order.

SELECT Customername
  FROM Customers
  WHERE EXISTS (SELECT Orderid
                  FROM Orders
                 WHERE Orders.Orderid = Customers.Orderid);

Result:

Example: 2

The following SQL statement returns TRUE and lists the customers who made a order on date '2020-09-19'.

SELECT Customername
  FROM Customers
  WHERE EXISTS (SELECT Orderid
                  FROM Orders
                  WHERE Orders.Orderid = Customers.Orderid
                    AND Orderdate='2020-09-19');

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!