Basic about IN Condition – using and example

This SQL tutorial explains how to use the SQL IN situation with syntax and examples.

Description

The SQL IN situation (sometimes called the IN operator) permits you to without problems test if an expression fits any cost in a list of values. It is used to assist minimize the want for a couple of OR stipulations in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in SQL is:

expression IN (value1, value2, .... value_n);

OR

expression IN (subquery);

Parameters or Arguments

expression This is a cost to test. value1, value2 …, alue_n These are the values to check towards expression. If any of these values suits expression, then the IN situation will evaluate to true. subquery This is a SELECT assertion whose result set will be tested against expression. If any of these values fits expression, then the IN condition will consider to true.

DDL/DML for Examples

If you prefer to observe along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your personal database!

Get DDL/DML

Example – Using the IN Condition with Character Values

The IN circumstance can be used with any information kind in SQL. Let’s seem at how to use the IN situation with persona (string) values.

In this example, we have a table referred to as suppliers with the following data:

supplier_id supplier_name city state 100 Microsoft Redmond Washington 200 Google Mountain View California 300 Oracle Redwood City California 400 Kimberly-Clark Irving Texas 500 Tyson Foods Springdale Arkansas 600 SC Johnson Racine Wisconsin 700 Dole Food Company Westlake Village California 800 Flowers Foods Thomasville Georgia 900 Electronic Arts Redwood City California

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods');

There will be three information selected. These are the outcomes that you see:

supplier_id supplier_name city state 100 Microsoft Redmond Washington 300 Oracle Redwood City California 800 Flowers Foods Thomasville Georgia

This example would return all rows from the suppliers table the place the supplier_name is both Microsoft, Oracle or Flowers Foods. Because the * is used in the select, all fields from the suppliers desk would appear in the end result set.

It is equivalent to the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft'
OR supplier_name = 'Oracle'
OR supplier_name = 'Flowers Foods';

As you can see, the use of the IN condition makes the declaration less difficult to examine and extra environment friendly than the use of a couple of OR conditions.

Example – Using the IN Condition with Numeric Values

Next, let’s look at how to use the IN condition with numeric values.

In this example, we have a desk referred to as clients with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
WHERE customer_id IN (5000, 7000, 8000, 9000);

There will be four records selected. These are the effects that you see:

customer_id last_name first_name favorite_website 5000 Smith Jane digminecraft.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

This example would return all archives from the clients table the place the customer_id is both 5000, 7000, 8000 or 9000.

It is equivalent to the following SQL statement:

Try It

SELECT *
FROM customers
WHERE customer_id = 5000
OR customer_id = 7000
OR customer_id = 8000
OR customer_id = 9000;

Example – Using the IN Condition with the NOT Operator

Finally, let’s look at how to use the IN condition with the NOT operator. The NOT operator is used to negate a condition. When we use the NOT operator with the IN condition, we create a NOT IN condition. This will test to see if an expression is not in a list.

In this example, we have a table called merchandise with the following data:

product_id product_name category_id 1 Pear 50 2 Banana 50 3 Orange 50 4 Apple 50 5 Bread 75 6 Sliced Ham 25 7 Kleenex NULL

Enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread');

There will be four data selected. These are the outcomes that you must see:

product_id product_name category_id 3 Orange 50 4 Apple 50 6 Sliced Ham 25 7 Kleenex NULL

This instance would return all rows from the merchandise table the place the product_name is no longer Pear, Banana or Bread. Sometimes, it is extra environment friendly to listing the values that you do now not want, as opposed to the values that you do want.

It is equivalent to the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_name <> 'Pear'
AND product_name <> 'Banana'
AND product_name <> 'Bread';

As you can see, the equal announcement is written the usage of AND stipulations alternatively of OR conditions because the IN situation is negated.