SQL SELECT – Mastering Data Querying in SQL

The SELECT statement is the most commonly used SQL command and is central to querying and analyzing data stored in relational databases. It enables users to retrieve specific data from one or more tables using flexible and powerful criteria.

In this chapter, we’ll explore the SELECT command in exhaustive detail — covering its syntax, clauses (WHERE, ORDER BY, GROUP BY, HAVING, LIMIT), joins, subqueries, aggregate functions, and common query patterns. Whether you’re performing basic lookups or building complex analytical queries, mastering SELECT is essential.

Basic Syntax of SELECT

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column ASC|DESC]
[LIMIT number];

Let’s break down each part and explore it in-depth.

Retrieving Specific Columns

Instead of retrieving all columns (SELECT *), specify the exact columns needed to optimize performance and clarity.

SELECT Name, Salary FROM Employees;

Avoid SELECT * in production queries unless necessary, as it may:

• Reduce performance
• Break applications if schema changes
• Retrieve unnecessary data

Filtering Data with WHERE

The WHERE clause filters rows based on specified conditions.

SELECT * FROM Products WHERE Price > 500;

Operators:

• Comparison: =, !=, <, >, <=, >=
• Logical: AND, OR, NOT
• Range: BETWEEN ... AND ...
• List: IN (...)
• Pattern: LIKE, ILIKE (PostgreSQL)
• Null check: IS NULL, IS NOT NULL

Examples:

SELECT * FROM Orders WHERE Status = 'Shipped';
SELECT * FROM Employees WHERE Department = 'HR' AND Age > 30;
SELECT * FROM Products WHERE Name LIKE 'Samsung%';

Using Expressions and Aliases

Expressions allow mathematical operations and text manipulation. Aliases (AS) rename columns in output.

SELECT Name, Salary * 12 AS AnnualSalary FROM Employees;

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Customers;

Sorting Results with ORDER BY

The ORDER BY clause arranges rows in ascending (ASC) or descending (DESC) order.

SELECT Name, Price FROM Products ORDER BY Price DESC;

Sort by multiple columns:

SELECT * FROM Employees ORDER BY Department ASC, Salary DESC;

Aggregate Functions

Aggregate functions compute summary values for multiple rows:

• COUNT() – number of rows
• SUM() – total of numeric column
• AVG() – average
• MIN() – minimum
• MAX() – maximum

Example:

SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

Grouping with GROUP BY

GROUP BY groups rows by one or more columns before applying aggregate functions.

SELECT Department, COUNT(*) AS DeptCount
FROM Employees
GROUP BY Department;

Use GROUP BY when you want summarized data per category (e.g., sales by region).

Filtering Groups with HAVING

HAVING filters the result of grouped records — similar to WHERE, but for aggregates.

SELECT Department, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

HAVING must be used after GROUP BY and before ORDER BY.

Joining Tables

SQL joins combine rows from two or more tables based on related columns.

Types of Joins:

• INNER JOIN – only matching rows
• LEFT JOIN – all rows from left + matches from right
• RIGHT JOIN – all rows from right + matches from left
• FULL OUTER JOIN – all rows from both sides
• CROSS JOIN – Cartesian product

Example – INNER JOIN:

SELECT Orders.OrderID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN:

SELECT Employees.Name, Departments.Name AS DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;

Subqueries (Nested SELECT)

A subquery is a SELECT statement within another SQL statement.

Example – in WHERE clause:

SELECT Name FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Example – in FROM clause:

SELECT Region, AVG(TotalSales) FROM (
  SELECT Region, SUM(SaleAmount) AS TotalSales
  FROM Sales
  GROUP BY Region
) AS RegionalSales
GROUP BY Region;

Subqueries can be correlated (depend on outer query) or independent.

DISTINCT – Eliminating Duplicates

DISTINCT removes duplicate rows from the result.

SELECT DISTINCT Department FROM Employees;

Combining multiple columns:

SELECT DISTINCT Country, City FROM Customers;

CASE Statement – Conditional Output

CASE allows you to apply conditional logic to your result set.

Example:

SELECT Name,
  CASE
    WHEN Salary >= 100000 THEN 'High'
    WHEN Salary >= 50000 THEN 'Medium'
    ELSE 'Low'
  END AS SalaryBand
FROM Employees;

CASE is a SQL substitute for IF-THEN-ELSE logic.

LIMIT and OFFSET – Paging Results

Use LIMIT to restrict the number of rows returned.

SELECT * FROM Products LIMIT 10;

OFFSET skips a number of rows:

SELECT * FROM Products ORDER BY ProductID LIMIT 10 OFFSET 10;

Useful for pagination in applications.

Performance Tips for SELECT

1. Use indexes on columns in WHERE, JOIN, ORDER BY
2. **Avoid SELECT *** unless absolutely needed
3. Use LIMIT to restrict large data retrievals
4. Avoid correlated subqueries in large datasets
5. Use EXPLAIN/ANALYZE to inspect query performance
6. Avoid functions on indexed columns in WHERE

Real-World Scenario: Sales Reporting Dashboard

Imagine a retail company using a Sales table with fields: SaleID, ProductID, CustomerID, Amount, SaleDate.

Query 1: Monthly Sales

SELECT EXTRACT(MONTH FROM SaleDate) AS Month, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY EXTRACT(MONTH FROM SaleDate)
ORDER BY Month;

Query 2: Top 5 Customers

SELECT Customers.Name, SUM(Amount) AS TotalSpent
FROM Sales
JOIN Customers ON Sales.CustomerID = Customers.CustomerID
GROUP BY Customers.Name
ORDER BY TotalSpent DESC
LIMIT 5;

Query 3: Low-performing Products

SELECT ProductID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(Amount) < 5000;

SELECT Query Structure Recap

Here’s the typical flow of a SELECT query:

1. FROM – Choose table(s)
2. JOIN – Combine additional tables
3. WHERE – Filter rows
4. GROUP BY – Aggregate by categories
5. HAVING – Filter aggregates
6. SELECT – Choose columns to return
7. ORDER BY – Sort rows
8. LIMIT – Return N rows

Understanding this logical order helps in debugging and building optimized queries.

Common Mistakes to Avoid

Mistake	Consequence
SELECT * in large tables	Wastes bandwidth, memory
Missing WHERE clause	Returns all rows
Filtering after GROUP BY	Use HAVING, not WHERE
Using incorrect JOIN type	Returns too many/few rows
Not indexing filter columns	Slows down performance

Summary

The SELECT statement is a powerful and versatile tool for data retrieval in SQL. Whether fetching raw rows, aggregating data, joining tables, or applying conditional logic, SELECT supports a wide range of analytical and reporting needs.

By mastering SELECT and its clauses, you gain the ability to:

• Query large datasets efficiently
• Perform deep analysis and summarization
• Combine and filter data from multiple tables
• Power dashboards, APIs, and applications

In the next chapter, we’ll explore Advanced Queries in SQL, including complex joins, nested subqueries, set operations (UNION, INTERSECT), and common table expressions (CTEs).