SQL SELECT IN

SQL SELECT IN Statement

The SQL SELECT IN statement is used to filter records based on a list of specified values. This command is essential for retrieving rows that match any value within a given set, streamlining the query process for multiple values.

---

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

• SELECT: This is the SQL keyword used to specify the columns to retrieve.
• column1, column2, ...: These specify the names of the columns to retrieve.
• FROM: This is the SQL keyword used to specify the table.
• table_name: This specifies the name of the table from which to retrieve the rows.
• WHERE: This is the SQL keyword used to specify the condition.
• column_name: This specifies the column to be filtered.
• IN (value1, value2, ...): This specifies the list of values to match in the column.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the SELECT IN statement to retrieve specific rows.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the SELECT IN Statement

This step involves retrieving rows from the employees table where the first_name is either 'John' or 'Jane'.

SELECT * FROM employees
WHERE first_name IN ('John', 'Jane');

This command will return the rows from the employees table where the first_name is 'John' or 'Jane'.

You can also use the IN clause with other columns and data types, such as numbers or dates.

For example, to retrieve rows where the hire_date is '2023-01-01' or '2023-02-01':

SELECT * FROM employees
WHERE hire_date IN ('2023-01-01', '2023-02-01');

This command will return the rows from the employees table where the hire_date is either '2023-01-01' or '2023-02-01'.