SELECT Queries and Filtering Data

Introduction

The SELECT statement is the foundation of retrieving data from a database. It allows users to fetch specific columns, apply conditions, sort results, and perform aggregations. In this tutorial, we will cover different ways to use SELECT queries, including filtering data using the WHERE clause.

Basic SELECT Statement

The simplest form of a SELECT query retrieves all columns from a table:

SELECT * FROM employees;

This query returns all rows and columns from the employees table.

Selecting Specific Columns

To retrieve only specific columns, list them in the SELECT clause:

SELECT first_name, last_name, department FROM employees;

This query fetches only the first_name, last_name, and department columns from the employees table.

Filtering Data Using WHERE

The WHERE clause is used to filter rows based on conditions:

SELECT * FROM employees WHERE department = 'IT';

This query retrieves all employees who belong to the IT department.

Using Comparison Operators

SQL provides several operators for filtering:

• = (equal to)
• != or <> (not equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)

Example:

SELECT * FROM employees WHERE salary > 50000;

This query retrieves employees with a salary greater than 50,000.

Using Logical Operators

Logical operators help in combining multiple conditions:

• AND: Both conditions must be true
• OR: At least one condition must be true
• NOT: Negates a condition

Example:

SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;

This retrieves IT department employees earning more than 50,000.

Filtering with IN, BETWEEN, and LIKE

IN: Matches any value in a given list

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

BETWEEN: Selects values within a given range

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

LIKE: Searches for a pattern in text columns

SELECT * FROM employees WHERE first_name LIKE 'A%';

This retrieves employees whose first name starts with 'A'.

Sorting Results Using ORDER BY

To sort query results, use the ORDER BY clause:

SELECT * FROM employees ORDER BY salary DESC;

This orders employees by salary in descending order.

To sort by multiple columns:

SELECT * FROM employees ORDER BY department ASC, salary DESC;

This sorts by department (ascending) and then by salary (descending).

Limiting Results Using LIMIT

The LIMIT clause restricts the number of rows returned:

SELECT * FROM employees LIMIT 5;

This fetches only the first 5 rows.

For databases like SQL Server, use TOP:

SELECT TOP 5 * FROM employees;

For Oracle, use FETCH FIRST:

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

Conclusion

The SELECT statement is a powerful tool in SQL for retrieving and filtering data. By combining different clauses like WHERE, ORDER BY, LIMIT, and logical operators, you can construct efficient queries to fetch exactly the data you need.