PostgreSQL

In PostgreSQL, the DQL (Data Query Language) allows us to retrieve data from the database using the SELECT statement. While querying, it's often necessary to apply conditions to filter the rows we want. This is where the AND and OR operators come into play. The AND operator ensures that all conditions specified must be true for a row to be included in the results. On the other hand, the OR operator allows any one of the conditions to be true for the row to be included. These operators are used in the WHERE clause of SQL queries to refine data selection.

Here’s how the AND and OR operators work:

1. UsingANDOperator

   The AND operator combines two or more conditions. Rows are returned only when all the specified conditions evaluate to true.

   • Example:

     Let's consider a table named customers with columns customer_id, first_name, last_name, and balance. If we want to retrieve customers whose balance is greater than 5000 and their last_name starts with 'S', we would write:

     SELECT customer_id, first_name, last_name, balance
     FROM customers
     WHERE balance > 5000
     AND last_name LIKE 'S%';
     

   • Explanation:
     • Condition 1: balance > 5000
     • Condition 2: last_name LIKE 'S%'
     • Both conditions must be true for a row to be included in the result.
2. UsingOROperator

   The OR operator allows any one of the conditions to be true for the row to be included. This is helpful when you want to retrieve rows that meet one or more alternative conditions.

   • Example:

     Suppose we want to retrieve customers who either have a balance less than 1000 or whose first_name is 'John':

     SELECT customer_id, first_name, last_name, balance
     FROM customers
     WHERE balance < 1000
     OR first_name = 'John';
     

   • Explanation:
     • Condition 1: balance < 1000
     • Condition 2: first_name = 'John'
     • A row is returned if either of these conditions is true.
3. CombiningANDandOROperators

   You can also combine the AND and OR operators in the same query to create complex filtering conditions. It's important to use parentheses to clearly define the precedence of conditions.

   • Example:

     If we want to find customers who either have a balance greater than 5000 and the last_name starts with 'S', or the first_name is 'John', we can write:

     SELECT customer_id, first_name, last_name, balance
     FROM customers
     WHERE (balance > 5000 AND last_name LIKE 'S%')
     OR first_name = 'John';
     

   • Explanation:
     • The conditions inside the parentheses will be evaluated first.
     • Either the combined condition inside the parentheses (balance > 5000 AND last_name LIKE 'S%') or first_name = 'John' must be true for a row to be included.
4. Practical Example with a Banking Scenario

   Let’s imagine we have a transactions table with columns transaction_id, account_id, transaction_date, amount, and transaction_type (credit or debit). We might want to find transactions that are either debit transactions greater than 5000 or credit transactions made after '2023-01-01'.

   • Example:

     SELECT transaction_id, account_id, transaction_date, amount, transaction_type
     FROM transactions
     WHERE (transaction_type = 'debit' AND amount > 5000)
     OR (transaction_type = 'credit' AND transaction_date > '2023-01-01');
     

   • Explanation:
     • This query returns rows where either:
       • The transaction_type is 'debit' and the amount is greater than 5000, or
       • The transaction_type is 'credit' and the transaction_date is after '2023-01-01'.

These examples show how the AND and OR operators can be used to filter data effectively, especially in complex queries involving multiple conditions.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT *
FROM org_client
WHERE gender = 'M'
AND state = 'NY';

SELECT *
FROM org_client
WHERE city = 'Miami'
OR state = 'PA';

SELECT *
FROM org_client
WHERE state = 'NY'
AND (city = 'Albany' OR credit_limit > 7000);

SQL's AND and OR operators

Here are examples illustrating the use of SQL's AND and OR operators.

Example 1 - Raw data from client table

Example 1 - AND operator

SELECT *
FROM org_client
WHERE gender = 'M'
AND state = 'NY';

Example 1 - Query data mapping

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. When using the AND operator, both column values must satisfy the specified criteria.

Example 1 - Query Output

Example 2 - OR operator

SELECT *
FROM org_client
WHERE city = 'Miami'
OR state = 'PA';

Example 2 - Query data mapping

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. In the case of the OR operator, at least one of the column values needs to meet the specified criteria.

Example 2 - Query Output

Example 3 - AND operator

SELECT *
FROM org_client
WHERE state = 'NY'
AND (city = 'Albany' OR credit_limit > 7000);

Example 3 - Query data mapping

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. In this instance, a specific row must satisfy the conditions based on the state, and subsequently, there should be a match in at least one value between the city and credit limit.

Example 3 - Query Output