Где не использовать NOT IN в MS SQL

Where Not In MS SQL

The WHERE NOT IN clause is a condition used in SQL queries to filter records based on a value that is not present in a specified list of values or a subquery result.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

Example:

Suppose we have a table called Customers:

We can use the WHERE NOT IN clause to retrieve the customers who do not have the specified IDs:

SELECT *
FROM Customers
WHERE CustomerID NOT IN (1, 3);

This query will return the following result:

In this example, we specified the values 1 and 3 in the NOT IN clause, so the query returns the customer with CustomerID 2, as they are the only ones who do not have these IDs.

Understanding the "WHERE NOT" Clause in MS SQL

When working with SQL databases, it is essential to understand the various query clauses that can be used to filter and retrieve specific data. One such clause is the "WHERE NOT" clause, which allows you to exclude certain rows from the result set based on specified conditions. In this article, we will explore how the "WHERE NOT" clause works in MS SQL and provide you with code examples to illustrate its usage.

The Basic Syntax of the "WHERE NOT" Clause

The "WHERE NOT" clause is used within the "SELECT" statement to specify a condition that must not be met for a row to be included in the result set. The basic syntax is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Here, "column1, column2, ..." refers to the columns you want to retrieve from the table, "table_name" is the name of the table you want to query, and "condition" is the condition that must not be met for a row to be included in the result set.

Let's dive into some code examples to understand how the "WHERE NOT" clause can be used in practice.

Example 1: Retrieving Employees Who Are Not Managers

Suppose we have a table called "Employees" that contains information about employees in a company. One of the columns in this table is "IsManager," which indicates whether an employee is a manager or not. To retrieve a list of employees who are not managers, we can use the following query:

SELECT *
FROM Employees
WHERE NOT IsManager = 1;

In this example, we specify the condition "NOT IsManager = 1," which means we want to retrieve all rows where the value of the "IsManager" column is not equal to 1. This effectively gives us a list of employees who are not managers.

Example 2: Excluding Rows Based on Multiple Conditions

The "WHERE NOT" clause can also be combined with other conditional operators, such as "AND" and "OR," to create more complex conditions. Let's consider a scenario where we want to exclude rows that satisfy multiple conditions from our result set.

Suppose we have a table called "Products" that contains information about various products. We want to retrieve all products that are not in stock and have a price above $100. We can use the following query to achieve this:

SELECT *
FROM Products
WHERE NOT (InStock = 1 AND Price > 100);

In this example, we use parentheses to group the conditions "InStock = 1" and "Price > 100" together. The "NOT" keyword negates the entire condition, resulting in rows that do not satisfy both conditions being included in the result set.

Example 3: Using the "NOT IN" Operator

In addition to comparing values using equality operators like "=" or "<>", the "WHERE NOT" clause can also be used with the "NOT IN" operator to exclude rows based on a list of values.

Let's consider a scenario where we have a table called "Customers" that contains information about customers. We want to retrieve all customers except those from a specific list of cities. We can use the following query:

SELECT *
FROM Customers
WHERE NOT City IN ('New York', 'Los Angeles', 'Chicago');

In this example, the "NOT IN" operator is used to exclude rows with the specified city values ('New York', 'Los Angeles', 'Chicago') from the result set.

Conclusion

The "WHERE NOT" clause in MS SQL provides a powerful way to exclude rows from a result set based on specified conditions. By understanding its syntax and usage, you can effectively filter and retrieve the data you need from your SQL databases. In this article, we explored the basic syntax of the "WHERE NOT" clause, demonstrated its usage with code examples, and discussed how it can be combined with other operators to create more complex conditions. Remember to experiment with different scenarios to further solidify your understanding of the "WHERE NOT" clause.