PostgreSQL: Using the AND Condition in WHERE Clauses

Learn how to use the `AND` operator in PostgreSQL to combine multiple conditions in `WHERE` clauses for precise data filtering.

Using the PostgreSQL `AND` Condition

Understanding the `AND` Condition

In PostgreSQL (and other SQL databases), the `AND` operator is used in the `WHERE` clause to combine multiple conditions. A row is selected only if *all* the conditions linked by `AND` are true. This is a fundamental tool for filtering data, allowing you to create highly specific queries that return only the data you need. The `AND` operator increases the selectivity of a query, returning a smaller, more targeted subset of data from your database.

`AND` Condition Syntax

The basic syntax is:

SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND ... AND conditionN;

Where each condition is an expression that evaluates to either true or false. The `AND` operator combines these conditions, and the row is selected only when all conditions are true.

Examples: Using `AND` in Different SQL Statements

These examples demonstrate the `AND` operator in various SQL statements, using different data types and table structures. These examples assume you have some sample tables already created and populated with data. The results will vary based on the data in your tables.

Example 1: `AND` with `INSERT`

This example inserts data into the `employee` table based on conditions in the `department` table. Only rows from `department` meeting the specified criteria will be inserted into `employee`.

INSERT INTO employee (phone, address)
SELECT phone, address
FROM department
WHERE dept_id < 5 AND department_name = 'SALES';

Example 2: `AND` with `SELECT`

This example selects client information based on `client_qualification` and `client_salary`.

SELECT client_name, client_profession, client_qualification, client_salary
FROM client
WHERE client_qualification = 'MBA' AND client_salary <= 1000000;

Example 3: `AND` with `JOIN`

This example joins two tables (`client` and `client_details`) using an `INNER JOIN` and filters the results based on a condition in the `client` table. This demonstrates how to use `AND` in a join query to combine and filter data from different tables.

SELECT 
    c.client_id, c.client_name, c.client_qualification, cd.mobile_number
FROM 
    client c
INNER JOIN 
    client_details cd ON c.client_id = cd.client_id
WHERE 
    c.client_qualification = 'MBA';

Example 4: `AND` with `UPDATE`

This example demonstrates using `AND` within an `UPDATE` statement to update only specific rows that meet several conditions.

UPDATE summer_fruits
SET summer_fruits_name = 'Strawberry'
WHERE fruit_id = 4 AND summer_fruits_name = 'Guava';

Example 5: `AND` with `DELETE`

This example shows using `AND` within a `DELETE` statement to delete only specific rows based on multiple criteria.

DELETE FROM customer
WHERE first_name = 'Jane' AND last_name = 'Miller';

Conclusion

The `AND` operator in PostgreSQL is fundamental for creating precise and targeted queries. It allows for flexible data filtering and manipulation, enhancing the power and effectiveness of your SQL statements.