PostgreSQL WHERE

The `WHERE` clause in PostgreSQL is used to filter records that meet specific conditions. It allows for retrieval of only those rows that fulfill the provided criteria.

Usage

The `WHERE` clause is typically used in conjunction with data retrieval commands like `SELECT`, `UPDATE`, and `DELETE` to specify which records should be affected. It comes after the table name and before other clauses like `ORDER BY` or `GROUP BY`.

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Offerto da

In this syntax, `WHERE condition` defines the filter criteria for the query, limiting the result set to only those rows that satisfy the condition.

Examples

1. Basic Filtering

sql
SELECT *
FROM employees
WHERE department = 'Sales';
Offerto da

This example retrieves all columns from the `employees` table for records where the `department` is 'Sales'.

2. Filtering with Multiple Conditions

sql
SELECT first_name, last_name
FROM employees
WHERE department = 'Engineering' AND salary > 50000;
Offerto da

Here, the query retrieves the `first_name` and `last_name` of employees in the 'Engineering' department with a salary greater than 50,000.

3. Using Pattern Matching

sql
SELECT product_name
FROM products
WHERE product_name LIKE 'A%';
Offerto da

This example uses the `LIKE` operator to fetch all product names starting with the letter 'A'.

4. Using WHERE in an UPDATE Statement

sql
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';
Offerto da

This query increases the salary by 10% for all employees in the 'Marketing' department.

5. Using WHERE in a DELETE Statement

sql
DELETE FROM orders
WHERE order_date < '2023-01-01';
Offerto da

This example deletes all orders placed before January 1, 2023.

6. Handling NULL Values

sql
SELECT * 
FROM customers 
WHERE last_name IS NULL;
Offerto da

This query retrieves all customers whose `last_name` is `NULL`.

7. Logical Operators Example

sql
SELECT employee_id
FROM employees
WHERE department = 'HR' OR department = 'Finance' AND NOT salary < 40000;
Offerto da

This query fetches employee IDs from either 'HR' or 'Finance' departments with a salary not less than 40,000.

Tips and Best Practices

• Use indexes. Ensure indexed columns are used in `WHERE` conditions for faster query performance. Be cautious when using functions or calculations on indexed columns as they can negate index benefits.
• Leverage logical operators. Combine multiple conditions using `AND`, `OR`, and `NOT` to create complex filters.
• Optimize conditions order. Place the most restrictive conditions first to reduce the dataset quickly.
• Employ pattern matching wisely. Use `%` and `_` with `LIKE` cautiously, as they can impact performance.
• Validate data types. Ensure that the data types in your `WHERE` clause condition match the column types to avoid errors and improve efficiency.
• Consider case sensitivity. Remember that PostgreSQL is case-sensitive in text comparisons, which can affect the results of `WHERE` conditions using strings.