Does EXISTS(subquery) really return TRUE?-CodePudding

As far as I know, EXISTS(subQuery) returns true when subQuery is nonempty. However, I am not sure if TRUE or FALSE values are the only values EXISTS returns. Consider the following piece of code:

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Query above returns 24 records.

Also, consider the following:

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products);

(Note that second query is essentially the same as:

SELECT SupplierName
FROM Suppliers

)

The Second Query now returns 29 records. If EXISTS only returns TRUE when subquery is nonempty, then there shouldn't be a difference between two queries. What's going on?

(You are welcome to try on your own here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_exists)

CodePudding user response：

In your first query, which I would write as:

SELECT SupplierName
FROM Suppliers s
WHERE EXISTS (
    SELECT 1
    FROM Products p
    WHERE p.SupplierID = s.supplierID AND Price < 20
);

the EXISTS clause only returns true if, for a given supplier record, we can find a matching product record with a price less than 20. This may not be true for every supplier record in the outer part of the query. On the other hand, in your second query:

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products);

the EXISTS clause will always return true assuming that the Products table has even one record in it. So your second query is logically the same as WHERE TRUE assuming Products is not empty.

The key point to understand here is that the exists clause will logically be evaluated for every record in the Suppliers table. It is not simply evaluated once, though in the case of the second query that could be done this way with the same result.

CodePudding user response：

The difference between the two queries is that the first query is checking for a specific condition (price < 20) within the subquery, while the second query is only checking for the existence of any records within the subquery. So in the first query, only suppliers with products that have a price less than 20 will be returned, while in the second query, all suppliers will be returned because there are products in the Products table. The EXISTS keyword is used to check if there are any records in the subquery that meet the specified condition, and if there are, it returns true and the main query returns the specified columns.

here is an example of using the EXISTS operator in SQL:

-- Example 1: Find all customers who have placed an order
SELECT * FROM Customers
WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);


-- Example 2: Find all products that have never been ordered
    SELECT * FROM Products
WHERE NOT EXISTS (SELECT * FROM Orders WHERE Orders.ProductID = Products.ProductID);

In this example, the outer query selects all rows from the Products table where no corresponding row exists in the Orders table that has a matching ProductID.

Keep in mind that the subquery in the EXISTS operator can be any SELECT statement that returns a result set. The only requirement is that the subquery returns at least one row for the EXISTS to evaluate to true.