为了账号安全,请及时绑定邮箱和手机立即绑定

mysql in语法介绍

标签:
MySQL

Summary: in this tutorial, you will learn how to use the MySQL IN operator that determines if a specified value matches any value a list or a subquery.

Introduction to the MySQL IN Operator

The IN operator allows you to determine if a specified value matches any one of a list or a subquery. The following illustrates the syntax of the IN operator.

SELECT column_list FROM table_name WHERE (expr|column) IN ('value1','value2',...)

In the query above:

  • You can use a column or an expression ( expr) with the IN operator in the WHERE clause of the SELECT statement.

  • The values in the list must be separated by a comma (,)

  • The IN operator can also be used in the WHERE clause of other statements such as INSERTUPDATEDELETE, etc.

The IN operator returns 1 if the value of the  column orthe result of the expr expression is equal to any value in the list, otherwise it returns 0.

When the values in the list are all constants:

  • First, MySQL evaluates the values based on the type of the column or result of the  expr.

  • Second, MySQL sorts the values.

  • Third, MySQL searches for values using binary search algorithm which is very fast.

Therefore a query that uses the IN operator with a list of constants will perform very fast.

If the expr or any value in the list is NULL, the IN operator returns NULL.

You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery.

Let’s practice with some examples of using the IN operator.

MySQL IN examples

If you want to find out all offices which locates in the U.S. and France, you can use the IN operator as the following query:

SELECT officeCode, city, phone FROM offices WHERE country IN ('USA','France')

mysql in example 1
You can achieve the same result with the OR operator as the following query:

SELECT officeCode, city, phone FROM offices WHERE country = 'USA' OR country = 'France'

In case the list has many values, you have to construct a very long statement with multiple OR operators. Hence the IN operator allows you to shorten the query and make the query more readable.

To get offices that does not locate in USA and France, you can use NOT IN in the WHERE clause as follows:

SELECT officeCode, city, phone FROM offices WHERE country NOT IN ('USA','France')

mysql in example 2

MySQL IN with subquery

The IN operator is often used with a subquery. For example, if you want to find order whose total amount is greater than $60K, you can use the IN operator as the following  query:

SELECT orderNumber,        customerNumber,        status,        shippedDate FROM orders  WHERE orderNumber IN ( SELECT orderNumber FROM   orderDetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000)

MySQL IN operator with subquery

In this tutorial, we have shown you how to use MySQL IN operator to determine if a value matches any value in a list or a subquery.

Related Tutorials

原文链接:http://outofmemory.cn/mysql/sql-in

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消