Home/Learn/SQL/SELECT & WHERE Clause

SELECT & WHERE Clause

Beginner

DML & Querying

SELECT retrieves columns from tables; WHERE filters rows using comparison, logical, and special operators before any results are returned.

Overview

SELECT is the most fundamental SQL statement. The logical query processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, meaning WHERE is evaluated on raw rows before any projection or aggregation happens. WHERE supports comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), and special forms (IS NULL, BETWEEN, IN, LIKE). Column aliases defined in SELECT are NOT available in WHERE because WHERE is processed first — a common gotcha. Filtering early in WHERE is critical for performance: it reduces the row set before any joins or aggregations.

Basic SELECT and WHERE Patterns

Project only the columns you need — SELECT * forces the engine to read all columns and transfers more data over the wire. Filter with WHERE to eliminate rows as early as possible.

SQL — SELECT/WHERE patterns and common mistakes

-- Bad: SELECT * reads all columns, including LOB columns (slow over network)
SELECT * FROM orders WHERE status = 'pending';

-- Good: project only what you need
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE status = 'pending';

-- Comparison operators
SELECT id, full_name, salary
FROM employees
WHERE salary > 75000
  AND department_id = 3
  AND hired_at >= '2020-01-01';

-- OR with parentheses (critical — AND binds tighter than OR)
SELECT id, email FROM users
WHERE (status = 'active' OR status = 'trial')
  AND created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Column alias NOT usable in WHERE (use the expression again)
SELECT id, total_amount * 1.18 AS total_with_tax   -- alias defined here
FROM orders
WHERE total_amount * 1.18 > 1000;                  -- repeat expression; alias not allowed

Logical Query Processing Order

Understanding the order in which clauses are logically evaluated explains many "why doesn't this work?" moments in SQL. The order is NOT the textual order.

SQL — logical query processing order explained

-- Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
-- Textual order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

-- Example demonstrating ORDER matters
SELECT
    department_id,
    COUNT(*)        AS emp_count,    -- (4) SELECT: alias defined
    AVG(salary)     AS avg_salary
FROM employees                       -- (1) FROM: full table
WHERE status = 'active'              -- (2) WHERE: filter rows (alias NOT available here)
GROUP BY department_id               -- (3) GROUP BY: aggregate
HAVING COUNT(*) > 5                  -- (5) HAVING: filter groups (alias available in PG)
ORDER BY avg_salary DESC             -- (6) ORDER BY: sort (alias available)
LIMIT 10;                            -- (7) LIMIT: truncate

-- Why aliases fail in WHERE but work in ORDER BY (PostgreSQL resolves this)
SELECT total_amount * 1.18 AS total_with_tax FROM orders
ORDER BY total_with_tax DESC;   -- works: ORDER BY sees SELECT aliases

Spring Data JPA Equivalent

Spring Data repository method names generate JPQL automatically. For complex WHERE clauses, use @Query with named parameters or Specifications.

Java — Spring Data JPA equivalents for SELECT/WHERE

// Spring Data JPA — derived query method (WHERE status = ? AND createdAt >= ?)
List<Order> findByStatusAndCreatedAtAfter(String status, LocalDateTime after);

// Explicit JPQL with named params
@Query("SELECT o FROM Order o WHERE o.status = :status AND o.totalAmount > :min")
List<Order> findExpensivePending(
    @Param("status") String status,
    @Param("min") BigDecimal minAmount
);

// Native SQL for complex predicates
@Query(value = """
    SELECT id, user_id, total_amount, created_at
    FROM orders
    WHERE status = :status
      AND created_at >= CURRENT_DATE - INTERVAL '30 days'
    ORDER BY created_at DESC
    """, nativeQuery = true)
List<Order> findRecentByStatus(@Param("status") String status);

Key Points to Remember

1Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
2WHERE is processed before SELECT, so column aliases are not available in WHERE.
3AND has higher precedence than OR — always use parentheses when mixing them.
4Avoid SELECT * in production queries; project only needed columns.
5Filter as early as possible in WHERE to reduce rows before joins and aggregations.
6In Spring Data JPA, complex WHERE clauses should use @Query or Specifications.

Interview Questions

Why can't you use a SELECT alias in the WHERE clause?

EasyAmazon

Explain the logical query processing order in SQL.

MediumGoogle

What is the difference between WHERE and HAVING?

EasyFlipkart

How does AND vs OR operator precedence affect query results?

MediumMicrosoft

What are the performance implications of SELECT * in a high-traffic API?

MediumUber

Ask Aria about SELECT & WHERE Clause

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Stored Procedures & Functions in SQL

ORDER BY, LIMIT & OFFSET