为什么SQL查询工作,即使SELECT缺少一个FROM,这将是一个语法错误.另请注意,查询仅显示满足WHERE子句的查询.
CREATE TABLE Customer_Tbl
(
CustomerName VARCHAR(50),Address VARCHAR(250),Country VARCHAR(50)
);
INSERT INTO Customer_Tbl
VALUES
('AAA','','Philippines'),('BBB','Mexico'),('CCC',('DDD',('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'
);
这是对sql Server 2012和2014的测试,这里有一个在线示例：http://rextester.com/GDGB80815
解决方法
sql Server在SELECT语句中不要求FROM.
SELECT 2 AS t WHERE 0 = 0
它返回一行,列为t和值2.
你可以写简单
SELECT 2 AS t
得到相同的结果.
您的查询与此相同：
SELECT *
FROM Customer_Tbl
WHERE EXISTS(
SELECT 2 AS Customer_Tbl
WHERE Customer_Tbl.Country = 'MEXICO'
);
Inner Customer_Tbl是具有常量2的列的别名.WHERE中的国家是外部表Customer_Tbl的列.
使用AS进行别名是一个很好的做法,并用列表完全限定列.
当您尝试分开运行内部零件时
SELECT 2 Customer_Tbl WHERE Country = 'MEXICO'
它不是因为没有FROM,而是因为解析器不知道是什么国家：
Msg 207,Level 16,State 1,Line 2 Invalid column name ‘Country’.
为了完整起见,这里是sql Server从MSDN开始的SELECT语句的语法：
<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 > ]
可选子句用方括号[]表示.如你所见,几乎所有的子句都是可选的,包括FROM,除了SELECT关键字本身和< select_list> ;.
