Where exists

1

Hello,
I am learning SQL using W3School website.
On the page explaining the SQL EXISTS Operator they give this example:

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
...the substatement found after the WHERE EXISTS Operator does not work on his own while the full statement seems to work.

(see w3schools website, SQL tab, SQL Exists section -> sorry, I am not allowed to put a link)
The database used is the Northwind sample database.

It does not make sense to me.

Could someone help me understand that?

2

The inner query in the EXISTS clause is referring to the value of supplierId from the outer query in it's WHERE clause, so if you get rid of the outer query it is no longer valid SQL.

You could also write this as:

SELECT SupplierName
FROM Suppliers
WHERE SupplierId = ANY(SELECT supplierId FROM Products WHERE Price < 20);

Which possibly makes that clearer (and allows you to run the subquery idependently)

3

Thanks AndyC!!
I now understand why the subquery was not working on it's own.
Your alternative query makes sense to me (except than I still have trouble understanding the difference between ANY and EXISTS) but it does not wok when I run it using the online SQL editor from the W3 website...I am not sure why (the subquery works though).
This is the error message I am getting:

Blockquote
Error 1: could not prepare statement (1 near "SELECT": syntax error)
Blockquote

4

Odd, works for me if I replace the EXISTS sample with the =ANY code on the W3C site, though I'm not overly familiar with restrictions on that site.

As for the difference, there really isn't any difference between EXISTS and =ANY other than syntactic sugar. Behind the scenes they are implemented in exactly the same way. The advantage of the =ANY approach is just that it "lifts" the joining condition out of the sub query which makes things a little easier to read. EXISTS is, however, probably compatible across more different database engines and lets you deal with situations where there is no joining clause (rarer but possible in some circumstances), e.g.

Select * From #TableA Where Exists(Select * From #TableB)

Would return rows from #TableA if there are any rows in #TableB - you can't really re-write this one as an =ANY clause.

5

Indeed, it works with EXISTS instead of =ANY.
It's probably due to the restrictions on that site.

Thanks for the clarification EXISTS vs ANY

6

This is a correlated subquery where inner query is dependent on outer query.So if you try to execute inner query separately it wont execute on its own. So if inner query returns product names for suppliers only that supplier outer query would return.