Postgres Day 5 - Let’s Play with AND, OR, NOT Conditions

First, let us recap the keywords and commands seen so far, along with the above conditions.

SELECT	This statement returns all columns from the table mentioned in the FROM statement
FROM	Use this to choose the Table from which we require the columns
WHERE	Here, we mention the filtering condition

Operators/Keywords	Definition	Example
=	Check if the two values are the same.	SELECT * FROM students WHERE course_date = '2024-09-16'
>	Check if the value on the left is greater than the value on the right.	SELECT * FROM students WHERE course_date > '2020-12-31'
<	Checks if the value on the left is less than the value on the right.	SELECT * FROM students WHERE course_fees < 30000
>=	Check if the value on the left is greater than or equal to the value on the right.	SELECT * FROM students WHERE course_fees >= 20000
<=	Check if the value on the left is less than or equal to the value on the right.	SELECT * FROM students WHERE course_fees <= 10000
<>	Check if two values are not the same	SELECT * FROM students WHERE course_date <> '2024-09-16'
between and	Used to specify a range for numeric or date/time columns.	SELECT * FROM students WHERE course_date between '2023-01-01' and '2024-12-31'
in	Used to specify a list of known values to compare a column against.	SELECT * FROM students WHERE state in ('TN', 'DL')
AND	Used to specify that all conditions connected by AND must be true for a record to be included in the results	SELECT * FROM students WHERE state = ('DL') AND course_fees >= 25000
OR	Used to specify that at least one condition connected by OR must be true for a record to be included in the results.	SELECT * FROM students WHERE course_name like ('comp%') OR course_name like ('data%')
NOT	Inverts the logical state of a condition.	SELECT * FROM students WHERE course_name NOT like ('comp%')
like	Used for pattern matching in text columns: "%" will Match zero, one, or more characters. "_" will Matches a single character."	SELECT * FROM students WHERE state like '_L'
like	Similar to LIKE but performs case-insensitive comparisons.	SELECT * FROM students WHERE course_name like 'data%'

Assume that we want to retrieve information based on the filtering conditions of two columns. That’s what we call a complex query.

SELECT 
    first_name,
    last_name,
    address,
    phone,
    state,
    course_name,
    course_fees
FROM 
    students
WHERE 
    state = 'DL'
    AND course_fees >= 25000

Complex query

SELECT 
    first_name,
    last_name,
    address,
    phone,
    state,
    course_name,
    course_fees
FROM 
    students
WHERE 
    state IN ('DL', 'KA')
    AND course_fees >= 25000;

FROM students

SELECT *
FROM students
WHERE course_date >= '2024-01-01'
AND course_fees >= 20000
AND scholarship_amt >= 1000

Query

SELECT *
FROM students
WHERE course_name ILIKE ('comp%')
  AND course_fees >= 25000
  AND zip_code LIKE ('3_____');

SELECT 
    first_name,
    last_name,
    address,
    state,
    zip_code,
    course_name,
    course_fees
FROM 
    students
WHERE 
    (course_name ilike 'comp%' OR course_name ilike 'data%')
    AND course_fees >= 22000;

Course

Here, we will have results from either of the conditions mentioned using the 'OR' keyword.

SELECT 
    first_name,
    last_name,
    address,
    state,
    zip_code,
    course_name,
    course_fees,
    course_date
FROM 
    students
WHERE 
    course_date > ('2022-01-01')
    OR course_name ILIKE ('data%')
    OR last_name ILIKE ('S%')

OR keyword

SELECT 
    first_name,
    last_name,
    address,
    state,
    zip_code,
    course_name,
    course_fees,
    course_date
FROM 
    students
WHERE 
    state NOT IN ('DL', 'MH', 'KA', 'PB')
    AND course_name NOT LIKE ('% %');

Conditions mentioned

Now let’s say we do not want to view students whose address has the word ‘Lane’ as well as ‘View’:

SELECT 
    first_name,
    last_name,
    address,
    state,
    zip_code,
    course_name,
    course_fees,
    course_date
FROM 
    students
WHERE 
    state NOT IN ('DL', 'MH', 'KA', 'PB')
    AND course_name NOT LIKE ('% %')
    AND (address NOT ILIKE ('%lane%') AND address NOT ILIKE ('%view%'))

This might seem complex at first, but the brackets help us to define the order of precedence.

Lane

Similarly, we want all the students whose state is DL, and course_fee is exactly 26890.34 OR 22456.78.

SELECT 
    first_name,
    last_name,
    address,
    state,
    zip_code,
    course_name,
    course_fees,
    course_date
FROM 
    students
WHERE 
    state IN ('DL')
    AND (course_fees = 26890.34 OR course_fees = 22456.78);

DL and course

Note. Precedence and Parentheses.

NOT has a higher precedence than AND and OR. So, NOT (condition1 AND condition2) is evaluated before the AND.

Use parentheses to explicitly define the order of evaluation, especially when combining multiple operators.

That's it for today! We will catch up on the next blog with more math, and string concatenation.