SQL NOT IN Operator

Overview

• Perceive what the ‘NOT IN’ operator in SQL does.
• Be taught the syntax and purposes of the perform.
• Discover ways to use the ‘NOT IN’ perform in SQL by way of an instance.
• Be taught one of the best practices to observe and the way to keep away from widespread errors whereas utilizing ‘NOT IN’.

Fundamentals of SQL `NOT IN`

Think about you may have a basket full of garments. The NOT IN operator is like taking out particular colours of shirts. You can provide it a listing of colours to take away, like purple, blue, and inexperienced. This manner, you’re left with solely the shirts that aren’t these colours. It really works the identical method with information in SQL. You may inform it to exclude any entries that match sure values, like particular numbers and even outcomes from one other search.

Syntax of ‘NOT IN’ Perform in SQL

SELECT column_name(s)

FROM table_name

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

Pattern Information

Let’s perceive how ‘NOT IN’ operator works in SQL, by way of a real-world instance. For this, we first want some pattern information. We’ll be utilizing two tables in our instance: `college students` and `programs`.

Desk: college students

Create college students desk

CREATE TABLE college students (

    student_id INT PRIMARY KEY,

    student_name VARCHAR(50)

);

Create programs desk

CREATE TABLE programs (

    course_id INT PRIMARY KEY,

    course_name VARCHAR(50),

    student_id INT,

    FOREIGN KEY (student_id) REFERENCES college students(student_id)

);

Insert information into college students desk

INSERT INTO college students (student_id, student_name) VALUES

(1, 'John'),

(2, 'Alice'),

(3, 'Bob'),

(4, 'Carol'),

(5, 'David');

Insert information into programs desk

INSERT INTO programs (course_id, course_name, student_id) VALUES

(101, 'Math', 1),

(102, 'Science', 2),

(103, 'Historical past', 3),

(104, 'Artwork', NULL),

(105, 'Literature', 2);

Sensible Utility

Now let’s apply the ‘NOT IN’ SQL operator to our pattern information and see the way it works.

 1. Particular Values

Use the `NOT IN` perform to exclude rows that match any worth in a given listing of values.

Instance:

SELECT employee_id, employee_name

FROM staff

WHERE department_id NOT IN (1, 2, 3);

This question retrieves all staff who aren’t in departments 1, 2, or 3.

 2. With Subqueries

Use `NOT IN` operator with subqueries to exclude rows primarily based on a subquery outcome.

Instance:

SELECT student_id, student_name
FROM college students
WHERE student_id NOT IN (SELECT student_id FROM programs WHERE course_name="Science");

This question retrieves all college students who aren’t enrolled within the Science course.

Widespread Pitfalls and The right way to Keep away from Them

Now that you simply’ve understood the way to use the ‘NOT IN’ perform, let’s discover ways to keep away from among the most typical errors or errors that may happen whereas utilizing it.

1. Dealing with NULL Values

The `NOT IN` operator can behave unexpectedly if the listing comprises NULL values. Any comparability with NULL ends in UNKNOWN, which may result in no rows being returned.

Instance:

SELECT student_id, student_name
FROM college students
WHERE student_id NOT IN (1, 2, NULL);

To keep away from this, be certain that the listing or subquery doesn’t include NULL values.

Resolution:

SELECT student_id, student_name
FROM college students
WHERE student_id NOT IN (SELECT student_id FROM programs 
WHERE course_name="Science" AND student_id IS NOT NULL);

 2. Efficiency Points

Utilizing `NOT IN` with giant subqueries can result in efficiency points. Be certain that the subquery is optimized and the columns used within the subquery are listed.

Options to SQL `NOT IN`

Now let’s discover some SQL capabilities that can be utilized in sure circumstances as a substitute of the ‘NOT IN’ operator.

 1. `NOT EXISTS`

`NOT EXISTS` is usually extra environment friendly and handles NULL values extra gracefully.

Instance:

SELECT student_id, student_name
FROM college students s
WHERE NOT EXISTS (SELECT 1 FROM programs c 
WHERE s.student_id = c.student_id AND c.course_name="Science");

 2. LEFT JOIN with IS NULL

Utilizing a `LEFT JOIN` with `IS NULL` can even function an alternative choice to `NOT IN`.

Instance:

SELECT s.student_id, s.student_name
FROM college students s
LEFT JOIN programs c ON s.student_id = c.student_id AND c.course_name="Science"
WHERE c.student_id IS NULL;

Finest Practices for Utilizing the ‘NOT IN’ Operator

1. Keep away from NULL Values: Be certain that the listing or subquery used with `NOT IN` doesn’t include NULL values to keep away from surprising outcomes.
2. Optimize Subqueries: Be certain that the subqueries are optimized and the columns concerned are listed for higher efficiency.
3. Use Options When Acceptable: Think about using `NOT EXISTS` or `LEFT JOIN … IS NULL` when coping with giant datasets or when NULL values are concerned.

Conclusion

Consider NOT IN as a sieve in your information. It allows you to shake out undesirable info and retains solely the outcomes you want. By studying the ins and outs of NOT IN, like when to make use of it and what to be careful for, you possibly can turn into a professional at filtering information in your SQL queries. Utilizing it the fitting method makes your queries correct and environment friendly.

Regularly Requested Questions