How does an SQL Exists statement even work if the syntax is wrong?

Question

Why does this SQL query work even though the SELECT is missing a FROM, which would be a syntax error. Also note that the query only shows the one that satisfies the WHERE clause.

CREATE TABLE Customer_Tbl
(
    CustomerName VARCHAR(50), 
    Address VARCHAR(250),
    Country VARCHAR(50)
);


INSERT INTO Customer_Tbl 
VALUES
    ('AAA', '','Philippines'),
    ('BBB', '','Mexico'),
    ('CCC', '','Philippines'),
    ('DDD', '','Mexico'),
    ('EEE', '','Philippines');


SELECT *
FROM Customer_Tbl 
WHERE EXISTS(
    -- This is missing a FROM
    -- running it by itself is a syntax error.
    SELECT 2 Customer_Tbl 
    WHERE Country = 'MEXICO'
);

This is test on SQL Server 2012 and 2014, here's an online sample: http://rextester.com/GDGB80815

Answer 1

SQL Server doesn't require FROM in the SELECT statement.

For example, there is no syntax error in the following query:

SELECT 2 AS t
WHERE 0 = 0

It returns one row with column t and value 2.

You can write simple

SELECT 2 AS t

to get the same result.

---

Your query is the same as this:

SELECT *
FROM Customer_Tbl 
WHERE EXISTS(
    SELECT 2 AS Customer_Tbl 
    WHERE Customer_Tbl.Country = 'MEXICO'
);

Inner Customer_Tbl is an alias for the column with the constant 2. The Country in WHERE is a column of an outer table Customer_Tbl.

It is a good practice to use AS for aliases and fully qualify the columns with their table names.

---

When you try to run the inner part separately

SELECT 2 Customer_Tbl 
WHERE Country = 'MEXICO'

it fails not because there is no FROM, but because the parser doesn't know what is Country:

Msg 207, Level 16, State 1, Line 2 Invalid column name 'Country'.

---

For the sake of completeness, here is the syntax of SELECT statement in SQL Server from MSDN:

<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]   

Optional clauses are in square brackets [ ]. As you can see, pretty much all clauses are optional, including FROM, except the SELECT keyword itself and < select_list >.

Answer 2

It's valid. It's correlating back to Customer_Tbl.Country

If there is at least one record in Customer_Tbl.Country='MEXICO' then the WHERE EXISTS is TRUE

I'ts only valid when it's inside the EXISTS though