PostgreSQL WHERE Clause

PostgreSQL WHERE Clause

The PostgreSQL WHERE clause is used to filter records in a SQL statement. This clause is essential for specifying conditions that determine which rows to retrieve, update, or delete from a table.

---

Syntax

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

The WHERE clause has the following components:

• column1, column2, ...: The columns to retrieve from the table.
• table_name: The name of the table from which to retrieve data.
• condition: The condition to filter rows.

---

Example PostgreSQL WHERE Clause Queries

Let's look at some examples of PostgreSQL WHERE clause queries:

1. Basic WHERE Clause Example

SELECT first_name, last_name
FROM employees
WHERE department_id = 1;

This query retrieves the first_name and last_name columns from the employees table where the department_id is 1.

2. WHERE Clause with Multiple Conditions

SELECT first_name, last_name
FROM employees
WHERE department_id = 1 AND salary > 50000;

This query retrieves the first_name and last_name columns from the employees table where the department_id is 1 and the salary is greater than 50000.

3. WHERE Clause with OR Condition

SELECT first_name, last_name
FROM employees
WHERE department_id = 1 OR salary > 60000;

This query retrieves the first_name and last_name columns from the employees table where the department_id is 1 or the salary is greater than 60000.

4. WHERE Clause with LIKE Condition

SELECT first_name, last_name
FROM employees
WHERE email LIKE '%@example.com';

This query retrieves the first_name and last_name columns from the employees table where the email ends with @example.com.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the WHERE clause to filter data.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, salary, and department_id.

Step 2: Inserting Data into the Table

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

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000, 2);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jim', 'Brown', 'jim.brown@example.com', 55000, 1);

Here, we insert data into the employees table.

Step 3: Using the WHERE Clause

This step involves using the WHERE clause to filter data in the employees table.

Basic WHERE Clause

SELECT first_name, last_name
FROM employees
WHERE department_id = 1;

WHERE Clause with Multiple Conditions

SELECT first_name, last_name
FROM employees
WHERE department_id = 1 AND salary > 50000;

WHERE Clause with OR Condition

SELECT first_name, last_name
FROM employees
WHERE department_id = 1 OR salary > 60000;

WHERE Clause with LIKE Condition

SELECT first_name, last_name
FROM employees
WHERE email LIKE '%@example.com';

These queries demonstrate how to use the WHERE clause to filter data in the employees table, including using single and multiple conditions, OR conditions, and the LIKE condition for pattern matching.

Conclusion

The PostgreSQL WHERE clause is a fundamental tool for filtering records in SQL statements. Understanding how to use the WHERE clause and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.