PostgreSQL
Comparison Operators
PostgreSQL Comparison Operators
In PostgreSQL, comparison operators are used to compare values within SQL queries. These operators help you filter data by comparing columns to specific values or between columns themselves. They are essential for writing queries that retrieve data based on certain conditions. Understanding these operators is fundamental for beginners who are learning to manipulate and query databases effectively.
Here’s a brief overview of the key comparison operators available in PostgreSQL:
- Equal to (
=):- Checks if two values are equal.
- Example:
SELECT * FROM customers WHERE customer_id = 1;
- Not Equal to (
!=or<>):- Checks if two values are not equal.
- Example:
SELECT * FROM accounts WHERE balance != 0; - Example:
SELECT * FROM transactions WHERE transaction_type <> 'credit';
- Greater Than (
>):- Checks if a value is greater than another.
- Example:
SELECT * FROM transactions WHERE amount > 1000;
- Less Than (
<):- Checks if a value is less than another.
- Example:
SELECT * FROM accounts WHERE balance < 500;
- Greater Than or Equal To (
>=):- Checks if a value is greater than or equal to another.
- Example:
SELECT * FROM transactions WHERE amount >= 500;
- Less Than or Equal To (
<=):- Checks if a value is less than or equal to another.
- Example:
SELECT * FROM accounts WHERE balance <= 1000;
- IS NULL:
- Checks if a value is NULL (i.e., missing or undefined).
- Example:
SELECT * FROM customers WHERE phone_number IS NULL;
- IS NOT NULL:
- Checks if a value is not NULL.
- Example:
SELECT * FROM transactions WHERE transaction_date IS NOT NULL;
These operators allow you to construct powerful queries that can retrieve precisely the data you need based on your specific criteria.
To gain complete access, login with gmail or outlook, no need of signup. click here
TEST CODE
To fetch records for clients identified as female, use the following query:
SELECT *
FROM org_client
WHERE gender = 'F';To fetch records for clients identified as male, filtering out those who are female, use the following query:
SELECT *
FROM org_client
WHERE gender <> 'F';SELECT * FROM org_employee WHERE salary > 150000 ORDER BY salary;
SELECT *
FROM org_employee
WHERE salary > 150000
ORDER BY salary;To fetch records for employees with salaries below 150,000, excluding the amount of 150,000 itself, use the following query:
SELECT *
FROM org_employee
WHERE salary < 150000
ORDER BY salary;To fetch records for employees with salaries equal to or greater than 150,000, including the amount of 150,000 itself, use the following query:
SELECT *
FROM org_employee
WHERE salary >= 150000
ORDER BY salary;To fetch records for employees with salaries equal to or less than 150,000, including the amount of 150,000 itself, use the following query:
SELECT *
FROM org_employee
WHERE salary <= 150000
ORDER BY salary;To retrieve clients whose birth year falls within the range of 1985 and 1995 using the BETWEEN SQL operator, use the following query:
SELECT *
FROM org_client
WHERE birth_year BETWEEN 1985 AND 1995;To retrieve all rows from the employees table where the designation column matches any of the specified values ('Sales Manager', 'Financial Analyst', 'CFO') using the IN operator, use the following query:
SELECT *
FROM org_employee
WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');To retrieve entries for clients with last names that begin with 'Me%', where '%' signifies the possibility of zero or more characters following 'Me', use the following query:
SELECT *
FROM org_client
WHERE last_name LIKE 'Me%';To retrieve entries for clients whose first names do not conclude with 'e', use the following query:
SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';To retrieve records for orders that have not been shipped yet using the IS NULL operator on a date column, use the following query:
SELECT *
FROM act_order
WHERE shipped_date IS NULL;To retrieve records for orders that have been shipped using the IS NOT NULL operator on a date column, use the following query:
SELECT *
FROM act_order
WHERE shipped_date IS NOT NULL;To retrieve all rows from the employees table where the designation column does not correspond to any of the specified values ('Sales Manager', 'Financial Analyst', 'CFO') using the NOT IN operator, use the following query:
SELECT *
FROM org_employee
WHERE designation NOT IN ('Sales Manager', 'Financial Analyst', 'CFO');Equal To (=)
SELECT *
FROM org_client
WHERE gender = 'F';
In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.
IN OPERATOR
SELECT *
FROM org_employee
WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');
>In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.
BETWEEN OPERATOR
SELECT *
FROM act_order
WHERE order_date BETWEEN '2023-12-10' AND '2023-12-17';
>In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.
LIKE OPERATOR
SELECT *
FROM org_client
WHERE last_name LIKE 'Me%';
>In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. Retrieve entries for clients with last names that begin with 'Me%', where '%' signifies the possibility of zero or more characters following 'Me'.
IS NULL OPERATOR
SELECT *
FROM prd_product
WHERE description IS NULL;
In the provided image, the green color signifies the data that has been selected or satisfies he conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. NOTE that description value for product id 7 and 9 compose a empty string, this is not a null.
Comments Not Found