PostgreSQL - WHERE clause

The WHERE clause in PostgreSQL, as in other relational database systems, is used to filter the result set of a SELECT, UPDATE, or DELETE statement. It specifies a condition that records/rows must meet to be included in the result set or affected by an UPDATE or DELETE command.

Syntax:

For a SELECT statement:

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

For an UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For a DELETE statement:

DELETE FROM table_name
WHERE condition;

Examples:

Basic Condition

Retrieve all employees with a salary greater than 50,000:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Using Logical Operators

Retrieve all employees in department 'Sales' or 'HR':

SELECT first_name, last_name, department
FROM employees
WHERE department = 'Sales' OR department = 'HR';

Combining Conditions

Retrieve all employees in department 'IT' with a salary greater than 60,000:

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'IT' AND salary > 60000;

Using the IN Keyword

Retrieve all employees in specific departments:

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Finance', 'Marketing');

Using the LIKE Keyword

Find all employees whose first name starts with 'Jo':

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'Jo%';

Update with WHERE
Increase the salary of employees in the 'IT' department by 10,000:
```
UPDATE employees
SET salary = salary + 10000
WHERE department = 'IT';
```
Delete with WHERE
Delete all records of employees who left the company:
```
DELETE FROM employees
WHERE status = 'Left';
```

Notes:

Always be careful when using the UPDATE and DELETE statements, especially without a WHERE clause. Not including a WHERE clause in UPDATE will modify all rows, and omitting it in DELETE will remove all rows from the table.
Use placeholders and parameterized queries to prevent SQL injection when building SQL statements dynamically or using user input in the WHERE clause.

In conclusion, the WHERE clause is a fundamental component of SQL in PostgreSQL, allowing for the fine-tuning of query results and precisely targeting data modifications.

PostgreSQL WHERE clause example: Use the WHERE clause to filter rows in a query:
```
SELECT * FROM employees WHERE department = 'IT';
```
How to use WHERE clause in PostgreSQL: Apply the WHERE clause in a simple SELECT statement:
```
SELECT * FROM products WHERE price > 50;
```
Conditional filtering in PostgreSQL WHERE clause: Filter data based on a condition in the WHERE clause:
```
SELECT * FROM orders WHERE order_status = 'Shipped';
```
Multiple conditions in WHERE clause PostgreSQL: Use multiple conditions with AND in the WHERE clause:
```
SELECT * FROM customers WHERE country = 'USA' AND status = 'Active';
```
Using LIKE in WHERE clause PostgreSQL: Perform pattern matching with the LIKE operator:
```
SELECT * FROM products WHERE product_name LIKE 'Laptop%';
```

PostgreSQL WHERE clause with AND/OR: Combine AND and OR operators for complex conditions:

SELECT * FROM employees WHERE (department = 'IT' AND experience > 5) OR (department = 'HR');

NULL values in WHERE clause PostgreSQL: Filter rows based on NULL or NOT NULL values:
```
SELECT * FROM customers WHERE email IS NULL;
```
Date and time conditions in WHERE clause PostgreSQL: Filter data based on date and time conditions:
```
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';
```

Subquery in WHERE clause PostgreSQL: Use a subquery in the WHERE clause for advanced filtering:

SELECT * FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');