SQL WHERE Clause

SQL WHERE Clause

The SQL WHERE clause is used to filter records based on specified conditions. This command is essential for retrieving specific rows that meet certain criteria, allowing for precise data queries and efficient data management.

---

Syntax

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

• 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 for filtering the rows.
• condition: This specifies the condition that the rows must meet to be retrieved.

---

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 WHERE clause to filter 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 WHERE Clause

This step involves retrieving rows from the employees table where the last_name is 'Doe'.

SELECT * FROM employees
WHERE last_name = 'Doe';

This command will return the rows from the employees table where the last_name is 'Doe'.

You can also use the WHERE clause with other conditions and comparison operators, such as:

• = (equal to)
• != or <> (not equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)
• LIKE (pattern matching)
• IN (matching any value in a list)
• BETWEEN (within a range of values)

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

SELECT * FROM employees
WHERE hire_date > '2023-02-01';

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