PostgreSQL WHERE

PostgreSQL WHERE

PostgreSQL WHERE Clause

The WHERE clause in PostgreSQL is used to filter records from a table based on a specified condition. It is one of the most important parts of SQL queries as it helps restrict the rows returned by a SELECT, UPDATE, DELETE, or other SQL commands.

1. Basic Syntax of the WHERE Clause

SELECT column1, column2, ... FROM table_name WHERE condition;
  • condition: The criteria that must be met for rows to be included in the result.
  • Example condition: column_name = value, column_name > value, column_name LIKE pattern, etc.

2. Common Conditions Used with WHERE

Equality (=)

SELECT * FROM employees WHERE department = 'HR';
  • This query returns all employees who work in the "HR" department.

Inequality (!= or <>)

SELECT * FROM employees WHERE department != 'HR';
  • This returns employees not in the "HR" department.

Greater Than (>)

SELECT * FROM employees WHERE salary > 50000;
  • This fetches employees with a salary greater than 50,000.

Less Than (<)

SELECT * FROM employees WHERE salary < 50000;
  • This fetches employees with a salary less than 50,000.

Greater Than or Equal To (>=)

SELECT * FROM employees WHERE salary >= 50000;
  • Fetches employees earning 50,000 or more.

Less Than or Equal To (<=)

SELECT * FROM employees WHERE salary <= 50000;
  • Fetches employees earning 50,000 or less.

3. Logical Operators with WHERE

You can use logical operators to combine multiple conditions.

AND (Both conditions must be true)

SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
  • This returns employees who are in the "HR" department and earn more than 50,000.

OR (Either condition can be true)

SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';
  • Returns employees who are in either the "HR" or "Finance" department.

NOT (Negates the condition)

SELECT * FROM employees WHERE NOT department = 'HR';
  • Fetches employees not in the "HR" department.

4. Other Comparison Operators with WHERE

BETWEEN (Range Check)

SELECT * FROM products WHERE price BETWEEN 100 AND 500;
  • This fetches products with a price between 100 and 500 (inclusive).

LIKE (Pattern Matching)

SELECT * FROM customers WHERE first_name LIKE 'J%';
  • This returns customers whose first name starts with "J".

ILIKE (Case-Insensitive Pattern Matching

SELECT * FROM customers WHERE first_name ILIKE 'j%';
  • This returns customers whose first name starts with "j" (case-insensitive).

IN (Multiple Possible Values)

SELECT * FROM employees WHERE department IN ('HR', 'Finance');
  • Returns employees who are in either the "HR" or "Finance" department.

IS NULL (Check for NULL values)

SELECT * FROM employees WHERE email IS NULL;
  • Returns employees who do not have an email.

IS NOT NULL (Check for non-NULL values)

SELECT * FROM employees WHERE email IS NOT NULL;
  • Returns employees who have an email.

5. Example Queries Using WHERE

Example 1: Find Employees in 'Sales' Department

SELECT * FROM employees WHERE department = 'Sales';

Example 2: Find Products Between $20 and $100

SELECT * FROM products WHERE price BETWEEN 20 AND 100;

Example 3: Find Customers Whose Names Start with 'M'

SELECT * FROM customers WHERE first_name LIKE 'M%';

Example 4: Get Employees Who Have a Salary Greater Than $80,000

SELECT * FROM employees WHERE salary > 80000;

Example 5: Find Employees in 'HR' or 'Finance' Departments

SELECT * FROM employees WHERE department IN ('HR', 'Finance');

Example 6: Check for Employees with NULL Email

SELECT * FROM employees WHERE email IS NULL;

6. Combining Conditions with Parentheses

When using multiple logical operators, use parentheses to control the order of evaluation.

Example: Complex Condition

SELECT * FROM employees WHERE (department = 'HR' AND salary > 50000) OR (department = 'Finance' AND salary < 60000);
  • This finds employees in the "HR" department earning more than 50,000 or employees in the "Finance" department earning less than 60,000.

7. Summary

ConditionDescription
=Equal to
!= or <>Not equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
BETWEENRange of values
LIKEPattern matching (case-sensitive)
ILIKEPattern matching (case-insensitive)
INList of possible values
IS NULLCheck for NULL values
IS NOT NULLCheck for non-NULL values
ANDBoth conditions must be true
ORAt least one condition must be true
NOTNegates the condition
Would you like help building more complex queries with WHERE clauses? 🚀
Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close