使用IN关键字。救命！ [英] Using IN Keyword. Help!

问题描述

嗨！



我是MS SQL的新手！我对 IN 关键字有疑问。



请解释这些问题的区别



使用 IN 关键字

  SELECt  *  FROM 产品
  WHERE  UnitPrice  IN （ SELECT  UnitPrice  FROM 产品其中 UnitPrice> 10） 

没有 IN 关键字

 < span class =code-keyword> SELECt  *  FROM 产品
  WHERE  UnitPrice =（ SELECT  UnitPrice  FROM 产品其中 UnitPrice> 10）

提前致谢！

解决方

简单：首先将工作，第二个将失败



这是一个概括，但它也是事实。当UnitPrice大于10时，第二个将失败，因为子查询

  SELECT  UnitPrice  FROM 产品其中 UnitPrice> 10 

将返回更多超过一个值，并且相等测试无法应对。



IN 子句说匹配任何一个这些值因此它适用于UnitPrice大于10的一个或多个产品。

这里

  SELECt  *  FROM 产品
  WHERE  UnitPrice =（ SELECT  UnitPrice  FROM 产品其中 UnitPrice > 10）

SELECT UnitPrice FROM Products Wh当UnitPrice> 10时，如果此查询返回多个值，则会抛出错误。这意味着您可以为Unitprice分配多个值

。但是如果您在运营商i中使用它将检查所选的单价并获得适当的单价。

喜欢

  SELECt  *  FROM 产品
  WHERE  UnitPrice  IN （ 10 ， 11 ， 12 ， 13 ）

//不会抛出错误

 <   pre     lang   =  sql >  SELECt * FROM Products 
 WHERE UnitPrice =（10,11,12,13）<   / pre  >  

//抛出错误

IN vs. JOIN vs. EXISTS [ ^ ]是一篇文章，可以帮助您理解这两个查询在性能和其他因素方面的差异。

Hi!

I''m a newbie to MS SQL! I have a doubt regarding IN Keyword.

Please explain the difference in these queries

With IN Keyword

SELECt * FROM Products
WHERE UnitPrice IN (SELECT UnitPrice FROM Products Where UnitPrice >10)

Without IN Keyword

SELECt * FROM Products
WHERE UnitPrice = (SELECT UnitPrice FROM Products Where UnitPrice >10)

Thanks in advance!

解决方案

Simple: The first will work, the second will fail.

That''s a generalisation, but it''s also true. The second will fail the moment there is more than one Product where the UnitPrice is greater than 10, because the subquery

SELECT UnitPrice FROM Products Where UnitPrice >10

will return more than one value, and the equality test cannot cope with that.

The IN clause says "matches any of these values" so it will work with one or more Products where the UnitPrice is greater than 10.

here

SELECt * FROM Products
WHERE UnitPrice = (SELECT UnitPrice FROM Products Where UnitPrice >10)

SELECT UnitPrice FROM Products Where UnitPrice >10 if this query return more than one value error will throw. that means you can assign more than one value to Unitprice
.but if you use in operator i it will check the unit price selected and will get appropriate one.
like

SELECt * FROM Products
WHERE UnitPrice IN (10,11,12,13)

// willnot throw error

<pre lang="sql">SELECt * FROM Products
WHERE UnitPrice =(10,11,12,13)</pre>

//throw error

IN vs. JOIN vs. EXISTS[^] is an article that should help you understand the difference between these two queries in terms of performance and other factors.

这篇关于使用IN关键字。救命！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！