Using WHERE, ORDER BY, and LIMIT in PostgreSQL Queries

In PostgreSQL, filtering, sorting, and limiting data retrieval are essential techniques for working efficiently with large datasets. The WHERE, ORDER BY, and LIMIT clauses help refine query results, improve performance, and optimize database interactions. In this tutorial, we will explore how to use these clauses effectively with the customers table.

1. Filtering Data with WHERE Clause

The WHERE clause filters records based on conditions, allowing you to retrieve only relevant data.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example: Find a customer by email

SELECT * FROM customers WHERE email = 'john.doe@example.com';

Example: Find customers from a specific city

SELECT * FROM customers WHERE address LIKE '%New York%';

Common Operators in WHERE Clause:

• =: Equal to
• != or <>: Not equal to
• > or <: Greater than or less than
• >= or <=: Greater than or equal to, less than or equal to
• LIKE: Pattern matching
• IN: Check if a value exists in a list
• BETWEEN: Range condition
• IS NULL: Check for NULL values

2. Sorting Results with ORDER BY

The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order.

Syntax:

SELECT column1, column2 FROM table_name ORDER BY column_name [ASC|DESC];

Example: Sort customers by last name alphabetically

SELECT first_name, last_name FROM customers ORDER BY last_name ASC;

Example: Sort customers by newest first

SELECT * FROM customers ORDER BY customer_id DESC;

Sorting by Multiple Columns

You can sort results by multiple columns.

SELECT first_name, last_name, date_of_birth FROM customers ORDER BY last_name ASC, date_of_birth DESC;

3. Limiting Results with LIMIT and OFFSET

The LIMIT clause restricts the number of rows returned, while OFFSET skips a specified number of rows.

Syntax:

SELECT column1, column2 FROM table_name LIMIT number OFFSET number;

Example: Retrieve only 5 customers

SELECT * FROM customers LIMIT 5;

Example: Paginate results (fetch next 10 rows after skipping the first 10)

SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 10;

4. Combining WHERE, ORDER BY, and LIMIT

You can combine these clauses for more efficient queries.

Example: Find the first 5 customers from New York sorted by last name

SELECT * FROM customers WHERE address LIKE '%New York%' ORDER BY last_name ASC LIMIT 5;

Example: Get the 3 most recent customers born after 1990

SELECT * FROM customers WHERE date_of_birth > '1990-01-01' ORDER BY date_of_birth DESC LIMIT 3;

Conclusion

Using WHERE, ORDER BY, and LIMIT effectively can improve query performance and help retrieve relevant data efficiently. Mastering these SQL clauses will enhance your ability to work with PostgreSQL databases. Stay tuned for the next tutorial, where we will explore more advanced querying techniques!