MySQL IN Clause


Introduction

The IN operator allows us to determine if a specified value matches any value in a set of values or returned by a subquery.

The following illustrates the syntax of the IN operator:

1
2
3
SELECT column1,column2,...
FROM table_name
WHERE (expr|column_1) IN ('value1','value2',...);

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

Examples

Suppose we want to know the producers who are also engineers. We can do this with the following nested query:

1
2
3
4
5
6
7
mysql> SELECT producer_name FROM producer WHERE producer_name
 -> IN (SELECT engineer_name FROM engineer);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+

For this particular example, we could also use a JOIN query:

1
2
3
4
5
6
7
mysql> SELECT producer_name FROM producer INNER JOIN engineer
 -> ON (producer_name = engineer_name);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+

We can use NOT IN to s find all the engineers who aren’t producers.

1
2
3
4
5
6
7
8
9
10
mysql> SELECT engineer_name FROM engineer WHERE
 -> engineer_name NOT IN
 -> (SELECT producer_name FROM producer);
+---------------+
| engineer_name |
+---------------+
| Eddie Kramer  |
| Jeff Jarratt  |
| Ed Stasium    |
+---------------+




Related Posts

MySQL INNER JOIN Clause

The INNER JOIN keyword selects records that have matching values...

MySQL Aliases

Aliases are nicknames. They give us a shorthand way of...

MySQL OR Operator

The OR operator combines two Boolean expressions and returns true...

MySQL LIMIT Operator

The LIMIT clause is used in the SELECT statement to...

MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to...

MySQL IS NULL Operator

To test whether a value is NULL or not, we...