PostgreSQL WHERE

Title: A comprehensive guide on using WHERE Clause in PostgreSQL with examples.

Author: Umair Anwar

Subject: Databases

Language: English

Source: PostgreSQL Documentation

In PostgreSQL, the WHERE clause is a fundamental component of SQL queries used to filter and retrieve specific data from a database table. In this post, we'll explore various types of WHERE queries in PostgreSQL using the popular db client, pgAdmin 4.

Basic WHERE Clause

The most common use of WHERE is to filter data based on a specific condition. For example, to retrieve all customers with a specific last name, you can use:

SELECT * FROM customers WHERE last_name='Guru';

Comparison Operators

PostgreSQL supports various comparison operators in the WHERE clause. Some examples include:

• = (Equal)
• <> or != (Not Equal)
• > (Greater Than)
• < (Less Than)
• >= (Greater Than or Equal To)
• <= (Less Than or Equal To) You can use these operators to filter data based on numeric, text, or date columns.

Logical Operators

PostgreSQL also supports logical operators in the WHERE clause. You can combine conditions using AND, OR, and NOT. For example:

SELECT * FROM courses WHERE title = 'PostgreSQL' AND price < 50;

Pattern Matching:

PostgreSQL provides pattern matching using the LIKE operator and wildcard characters % and _. For instance, to find all customers with a last name starting with 'Gur', you can use:

SELECT * FROM users WHERE last_name LIKE 'Gur%';

IN and NOT IN

The IN and NOT IN operators allow you to filter data based on a list of values. For example, to find products in a specific category:

SELECT * FROM courses WHERE category IN ('Backend', 'Frontend', 'DevOps');

NULL Values

To filter records where a specific column is NULL or NOT NULL, you can use the IS NULL and IS NOT NULL conditions. For example:

SELECT * FROM users WHERE is_staff IS NULL;

BETWEEN

The BETWEEN operator is used to filter data within a range of values. For instance, to retrieve upcoming events between two dates:

SELECT * FROM events WHERE start_date BETWEEN '2023-11-01' AND '2023-12-31';

SUB-QUERIES

You can use subqueries in the WHERE clause to filter data based on the result of another query. This is useful for complex filtering conditions. For example:

SELECT * FROM courses WHERE price > (SELECT AVG(price) FROM courses);

These are just some of the many possibilities when it comes to using the WHERE clause in PostgreSQL. Depending on your specific use case and data, you can craft complex queries to filter and retrieve the information you need from your database.