有2个关系：
Product (pname, price, cid)
Company(cid, cname, city)
插入元祖：
insert into Product values ('gizmo', 100, 1);
insert into Product values('powergizmo', 200, 1);
insert into Product values('iStuff', 500, 2);
insert into Product values('gadget', 300, 2);
insert into Product values('powergadget', 400, 2);
insert into Company values(1,'GizmoWorks','San Jose');
insert into Company values(2,'BigCompany','Boston');
insert into Company values(3,'PowerWorks','Seattle');
运行查询：
select distinct x.city,(select count(*)
from product y
where x.cid = y.cid)
from company x
和查询:
select distinct x.city,(select count(*)
from product y, company z
where z.cid = y.cid and z.city = x.city)
from company x
的结果一样，是巧合还是什么原因？这个z到底是必须的，还是可以省略的？
------解决方案--------------------
两个结果集确实是一样，不过这个只是巧合。总体说来，第一种方法更加准确一点，也比较简单。
为了说明，我把数据修改了一下，结果就不一样了。
drop table Product,Company
create table Product(pname varchar(100), price int, cid int)
create table Company(cid int,cname varchar(100),city varchar(100))
insert into Product values ('gizmo', 100, 1);
insert into Product values('powergizmo', 200, 1);
insert into Product values('iStuff', 500, 2);
insert into Product values('gadget', 300, 3); --修改cid为3
insert into Product values('powergadget', 400, 4); --修改cid为4
insert into Company values(1,'GizmoWorks','San Jose');
insert into Company values(2,'BigCompany','Boston');
insert into Company values(3,'PowerWorks','Seattle');
insert into Company values(4,'PowerWorks','Seattle'); --增加一条
select distinct x.city,(select count(*)
from product y
where x.cid = y.cid)
from company x
/*
city (无列名)
Boston 1
San Jose 2
Seattle 1
*/
select distinct x.city,(select count(*)
from product y, company z
where z.cid = y.cid and z.city = x.city)
from company x
/*
city (无列名)
Boston 1
San Jose 2
Seattle 2
*/
------解决方案--------------------
能否省略，主要看的是cid是否足够唯一标识，因为在select中再写select，需要保证唯一性，不然会报错：
Msg 512, Level 16, State 1, Line 16如果cid已经足够唯一标识，就可以不用city，否则就需要加上去
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.