在SQL 2005中对Exists运算符的怀疑 [英] Doubt in Exists operator in sql 2005

问题描述

我读到存在运算符不会返回数据，而是返回值true和false.即，如果existed查询中的条件为true或false.还存在并不检查每一行数据.一旦发现存在数据，则返回true，然后
第一个查询将相应地运行返回数据


但是事实并非如此.

在处理罗斯文数据库时
当我在查询后运行时，它返回了91行

从客户中选择customerid，companyname

但是当我在以下查询中使用exist运算符时，它仅返回89行

从存在的客户cu中选择customerid，companyname(从存在o.customerid = cu.customerid的订单o中选择orderid)


这意味着存在不仅检查特定数据集是否存在，还返回等于o.customerid
的所有cu.customer id.

请指导我有关Exists的信息
它返回什么值

I have read that the exists operator doesn''t return the data instead it returns the value true and false.i.e if condition in exist query is true or false. Also exist doesn''t check row by row data .As soon as it found that data exist it returns true and then
first query will run return data accordingly


however this was not the case .

when dealing with northwind database
when I was running following query it returned 91 rows

select customerid,companyname from customers

but when I used exist operator in following query it return only 89 rows

select customerid,companyname from customers cu where exists (select orderid from orders o where o.customerid=cu.customerid)


which means exist not only checks that particular set of data exist or not .it also returns all the cu.customer id which is equal to o.customerid


please Guide me regarding Exists operator
that what value it returns

推荐答案

您应该避免使用此运算符，因为根据定义，内部SQL对于返回的每个外部行都运行一次.它的价格昂贵.我不知道您正在读什么书，但是按照您描述的方式，这完全是没用的.它对返回的每一行都运行一次，并且只会返回有订单的客户.

You should avoid using this operator because the inner SQL is, by definition, run once for every outer row that is returned. It''s expensive. I don''t know what books you''re reading, but the way you describe it, it would be utterly useless. It''s being run once for every row returned, and it''s only going to return customers who have an order.

您得到了正确的结果，这是一个等价物:

从orders.customerid = customers.customerid
的客户内部加入订单中选择不同的customers.customerid，公司名称
您会发现在不使用DISTINCT的情况下执行的查询将返回830行

---有91位客户.

所以-

从客户那里选择customers.customerid，公司名称，其中customers.customerid不存在(从订单中选择customerid)

向我们显示有两个未下订单的客户.它们不存在于订单表中.

You''ve got the correct result, this is an equivalent:

select distinct customers.customerid, companyname from customers inner join orders on orders.customerid=customers.customerid

You''ll find the query executed without DISTINCT will return 830 rows

--- There are 91 customers.

So -

select customers.customerid, companyname from customers where customers.customerid NOT IN (select customerid from orders)

shows us there are two customers that have not placed an order. They don''t EXIST in the order table.

这篇关于在SQL 2005中对Exists运算符的怀疑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！