SQL IN Operator

     

❮ PreviousNext ❯

The SQL IN operator is used in SQL queries to specify a list of values that a column should match against. It is used in conjunction with the SELECT, WHERE, or HAVING clauses to filter or retrieve rows based on multiple values in a single query. The syntax for the SQL IN operator is as follows:

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

 

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, ...);

 where column_name is the name of the column you want to compare, and value1, value2, ..., valueN are the values you want to match against.

 Example: Suppose you have a "customers" table with a "country" column, and you want to retrieve all customers whose country is either "USA" or "Canada". You can use the following query:

 

SELECT * FROM customers

WHERE country IN ('USA', 'Canada');

 This will return all rows from the "customers" table where the "country" column matches either "USA" or "Canada".

You can also use subqueries with the IN operator to retrieve values from another table.

For example:

 

SELECT * FROM orders

WHERE customer_id IN (SELECT customer_id

FROM customers

WHERE country = 'USA');

This query retrieves all orders from the "orders" table where the "customer_id" is present in the result of the subquery, which retrieves the "customer_id" values from the "customers" table where the "country" is 'USA'.

IN Operator Examples

The following SQL statement selects all customers that are located in "Germany", "France" or "UK":

 

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

Example 

SELECT * FROM Customers

WHERE Country NOT IN ('Germany', 'France', 'UK');

 The following SQL statement selects all customers that are from the same countries as the suppliers:

Example 

SELECT * FROM Customers

WHERE Country IN (SELECT Country 

FROM Suppliers); 

Note: The IN operator is case-sensitive in most SQL databases, meaning that 'USA' and 'usa' would be considered different values. If you want to perform a case-insensitive comparison, you can use appropriate string functions or change the collation of the column or database.

 

❮ PreviousNext ❯

---