Welcome to our SQL IN Operator tutorial! Today, we'll learn about one of the most useful SQL operators for working with multiple values in a single query. 📝
The SQL IN Operator is a powerful tool that allows you to check whether a value is present in a predefined set of values. It's particularly useful when you need to filter records based on a list of values.
Let's start with a simple example. Suppose we have a table named Students with columns id and name.
CREATE TABLE Students (
id INT,
name VARCHAR(255)
);
INSERT INTO Students (id, name)
VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob'), (4, 'Charlie');To select the records of Alice and Bob, you can use the SQL IN Operator like this:
SELECT * FROM Students WHERE name IN ('Alice', 'Bob');This query will return records with names 'Alice' and 'Bob'.
The IN Operator is not limited to two values. You can include as many values as you want, separated by commas.
SELECT * FROM Students WHERE name IN ('Alice', 'Bob', 'Charlie');This query will return records with names 'Alice', 'Bob', or 'Charlie'.
You can also use the IN Operator to find records that do not match the specified values by using the NOT keyword.
SELECT * FROM Students WHERE name NOT IN ('John', 'Bob');This query will return records with names other than 'John' and 'Bob'.
The IN Operator can also be used with subqueries, which are SQL queries within a SQL query. This allows you to dynamically generate the list of values based on another query.
SELECT * FROM Students WHERE id IN (SELECT id FROM Students WHERE name = 'Alice');This query will return the records of all students with an id that matches the id of Alice (since Alice's id is 2, it will return only one record).
What does the SQL IN Operator do?
How can you use the NOT keyword with the IN Operator?
Can you use the IN Operator with subqueries?