sql doesn't contain | Code Ease (2024)

Solution 1:

In SQL, the "NOT IN" operator is used to exclude rows from a query result that match a specific condition. It is used in conjunction with the "SELECT" statement to retrieve data from a table.

Here is an example to demonstrate the usage of "NOT IN" in SQL:

Consider a table named "employees" with the following columns: "employee_id" (primary key), "employee_name", and "department_id".

Table: employees
+-------------+---------------+---------------+
| employee_id | employee_name | department_id |
+-------------+---------------+---------------+
| 1 | John | 1 |
| 2 | Mary | 2 |
| 3 | David | 1 |
| 4 | Lisa | 3 |
| 5 | Mark | 2 |
+-------------+---------------+---------------+

Let's say we want to retrieve all employees who do not belong to the department with department_id = 1. We can use the "NOT IN" operator to achieve this:

sql
SELECT * FROM employees
WHERE department_id NOT IN (1);

Output:
+-------------+---------------+---------------+
| employee_id | employee_name | department_id |
+-------------+---------------+---------------+
| 2 | Mary | 2 |
| 4 | Lisa | 3 |
| 5 | Mark | 2 |
+-------------+---------------+---------------+

In this example, the query selects all rows from the "employees" table where the "department_id" is not equal to 1. As a result, only the employees who belong to departments other than department_id = 1 are returned.

Note: The "NOT IN" operator can also be used with subqueries to exclude rows based on the result of another query.

Solution 2:

NOT IN vs. NOT CONTAIN

NOT IN and NOT CONTAIN are two operators in SQL that are used to test whether a value is not present in a specified set of values or a subquery.

NOT IN

The NOT IN operator tests for the absence of a value in a specified list of values. Its syntax is:

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

NOT CONTAIN

The NOT CONTAIN operator tests for the absence of a value in a subquery. Its syntax is:

sql
SELECT column_name
FROM table_name
WHERE column_name NOT CONTAIN (
 SELECT column_name
 FROM subquery
);

Key Differences

The main difference between NOT IN and NOT CONTAIN is:

* NOT IN compares a single value to a static list of values.
* NOT CONTAIN compares a value to the result set of a subquery.

Usage Examples

Consider the following table named Customers:

| CustomerID | CustomerName | City |
| ---------- | ---------- | ---------- |
| 1 | John Smith | New York |
| 2 | Jane Doe | Boston |
| 3 | Michael Jones | Los Angeles |
| 4 | Mary Johnson | Houston |

Example 1: NOT IN

To find customers who do not live in New York, you could use:

sql
SELECT CustomerID, CustomerName
FROM Customers
WHERE City NOT IN ('New York');

Output:

| CustomerID | CustomerName |
| ---------- | ---------- |
| 2 | Jane Doe |
| 3 | Michael Jones |
| 4 | Mary Johnson |

Example 2: NOT CONTAIN

To find customers who do not live in a city that starts with the letter 'A', you could use:

sql
SELECT CustomerID, CustomerName
FROM Customers
WHERE City NOT CONTAIN (
 SELECT DISTINCT LEFT(City, 1)
 FROM Customers
);

Output:

| CustomerID | CustomerName |
| ---------- | ---------- |
| 2 | Jane Doe |
| 4 | Mary Johnson |

Efficiency Considerations

* NOT IN is generally more efficient when the list of values is small.
* NOT CONTAIN is more efficient when the subquery returns a large number of rows.

Conclusion

Both NOT IN and NOT CONTAIN can be useful depending on the specific requirements of your query. Understanding the differences between them will help you optimize your SQL statements for efficiency and accuracy.

Solution 3:

In SQL, the "NOT IN" operator is used to exclude certain values from the result set of a query. It returns all the rows that do not have a specified value in a particular column.

Here's an example:

Suppose we have a table named "employees" with the following data:

ID | Name | Department | Salary
-------------------------------------
1 | John Doe | HR | 50000
2 | Jane Smith | IT | 60000
3 | Bob Brown | Marketing | 70000
4 | Alice Lee | HR | 55000
5 | Tom Jones | IT | 65000

If we want to find all the employees who are not in the IT department, we can use the following SQL query:

SELECT * FROM employees WHERE Department NOT IN ('IT');

This will return the following result:

ID | Name | Department | Salary
-------------------------------------
1 | John Doe | HR | 50000
3 | Bob Brown | Marketing | 70000
4 | Alice Lee | HR | 55000

Note that the "NOT IN" operator can also be used with subqueries. For example, if we want to find all the employees who have a higher salary than the average salary of the IT department, we can use the following SQL query:

SELECT * FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees WHERE Department = 'IT') AND Department NOT IN ('IT');

This will return the following result:

ID | Name | Department | Salary
-------------------------------------
3 | Bob Brown | Marketing | 70000

Note that the "NOT IN" operator returns NULL if the subquery returns no rows. Therefore, it is recommended to use the "NOT EXISTS" operator instead of "NOT IN" with subqueries, as it is more efficient and less prone to errors.

Here's an example of using the "NOT EXISTS" operator:

SELECT * FROM employees e1 WHERE Salary > (SELECT AVG(Salary) FROM employees e2 WHERE e2.Department = 'IT') AND NOT EXISTS (SELECT * FROM employees e3 WHERE e3.Department = 'IT' AND e3.Salary >= e1.Salary);

This will return the same result as the previous query, but it is more efficient and less prone to errors.