Filtering and Sorting Data in SQL

Published 3 months ago

When working with databases, retrieving specific records efficiently is crucial. SQL provides powerful tools to filter and sort data, allowing users to fetch only the needed information while organizing results meaningfully. This blog will cover key SQL techniques for filtering and sorting data, including:

Using the WHERE clause
Sorting with ORDER BY
Limiting results with LIMIT and OFFSET
Eliminating duplicates with DISTINCT
Pattern matching with LIKE and wildcards (%, _)

1. Using the `WHERE` Clause

The WHERE clause filters records that meet specific conditions. Without it, an SQL query retrieves all rows from a table, which can be inefficient for large datasets.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example:

Retrieve all customers from Nigeria:

SELECT * FROM customers WHERE country = 'Nigeria';

Operators Used with `WHERE`:

= (equal to)
!= or <> (not equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
BETWEEN (range of values)
IN (matches any value in a list)
AND, OR, NOT (combine conditions)

Example using multiple conditions:

SELECT * FROM orders WHERE status = 'Delivered' AND total_price > 100;

Here is an SQL query that uses BETWEEN, IN, AND, OR, and NOT together:

SELECT * 
FROM users 
WHERE age BETWEEN 18 AND 30 -- Selects users whose age is between 18 and 30
AND country IN ('USA', 'UK', 'Canada') -- Country must be one of these
AND (status = 'active' OR status = 'pending') -- Status can be active or pending
AND NOT role = 'admin'; -- Excludes users with the role of admin

Find employees with salaries between 50,000 and 100,000

SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 100000;

Find users from specific countries

SELECT * FROM users  WHERE country IN ('USA', 'Canada', 'Australia');

Get users who are either in New York or Los Angeles

SELECT * FROM users 
WHERE city = 'New York' OR city = 'Los Angeles';

Find users who are NOT from the USA

SELECT * FROM users WHERE NOT country = 'USA';

2. Using `ORDER BY`

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

Syntax:

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

Example:

Retrieve products sorted by price in descending order:

SELECT * FROM products ORDER BY price DESC;

Sort customers by country (ascending) and name (descending):

SELECT * FROM customers ORDER BY country ASC, name DESC;

3. Using `LIMIT` and `OFFSET`

LIMIT restricts the number of records returned. OFFSET skips a specified number of rows before fetching results.

Syntax:

SELECT column1, column2 FROM table_name LIMIT number OFFSET number;

Example:

Retrieve the first 5 customers:

SELECT * FROM customers LIMIT 5;

Retrieve the next 5 customers (pagination):

SELECT * FROM customers LIMIT 5 OFFSET 5;

4. Using `DISTINCT`

The DISTINCT keyword removes duplicate values from results.

Syntax:

SELECT DISTINCT column1 FROM table_name;

Example:

Retrieve unique customer countries:

SELECT DISTINCT country FROM customers;

5. Using `LIKE` and Wildcards (`%`, `_`)

The LIKE operator filters records based on pattern matching. Wildcards include:

% (matches any number of characters)
_ (matches a single character)

Syntax:

SELECT column1 FROM table_name WHERE column1 LIKE 'pattern';

Example:

Retrieve customers whose names start with "A":

SELECT * FROM customers WHERE name LIKE 'A%';

Retrieve emails ending in "@gmail.com":

SELECT * FROM customers WHERE email LIKE '%@gmail.com';

Retrieve products with a four-letter name starting with "B":

SELECT * FROM products WHERE name LIKE 'B___';

Conclusion

Filtering and sorting data in SQL improves query efficiency and data retrieval accuracy. By mastering WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, and LIKE, you can refine your SQL skills and handle complex database operations efficiently.

Happy Querying!

Obafemi Emmanuel