EXISTS in ANSI SQL. Table name in super query

Question

This is a classical EXISTS query :

SELECT S.SupplierName AS Supplier
FROM Suppliers S
WHERE EXISTS (SELECT P.ProductName FROM Products P WHERE P.SupplierId = S.supplierId AND P.Price < 20);

However, I've been told that although this query would work in almost every database engine, it is not allowed in ANSI SQL, because I have named a table in the subquery that is not explicitly named in the main query.

Something like this should be correct :

SELECT S.SupplierName AS Supplier
FROM Suppliers S, Products P --Yes, join is pending here
WHERE EXISTS (SELECT P2.ProductName FROM Products P2 WHERE P2.SupplierId = S.supplierId AND P2.Price < 20);

But that does not make sense to me either, but I have searched for an official ANSI SQL example to prove that it is not necessary to name the tables of the subquery in the main query and I have not found anything.

I would really appreciate if someone could tell me if the first query is ANSI SQL.

Answer 1

This query:

SELECT S.SupplierName AS Supplier
FROM Suppliers S
WHERE EXISTS (SELECT P.ProductName
              FROM Products P
              WHERE P.SupplierId = S.supplierId AND P.Price < 20
             );

is valid ANSI SQL. It is valid in every (reasonable) database that I know. I should add that I would use SELECT 1 in the subquery -- but the columns being selected don't matter from a syntactic perspective.

Wherever you learned this:

it is not allowed in ANSI SQL, because I have named a table in the subquery that is not explicitly named in the main query.

is incorrect.

I should note that although the comma is allowed in ANSI SQL, its use is discouraged. You should always use explicit JOIN syntax, in this case, CROSS JOIN.