Lesson 3 – Restricting and Sorting Data (or: teaching your queries to have standards) - SQL Guide

And look, a SELECT that returns every row in a table is technically correct, but it’s also the database equivalent of shouting “EVERYBODY IN HERE” and being surprised when you get trampled. In real life you almost always want some rows, in some order.

In this lesson you will learn to:

Limit which rows are returned using the WHERE clause.
Use comparison operators, ranges, lists, and pattern matching.
Handle NULL conditions properly.
Combine conditions with AND, OR, and NOT (without confusing yourself).
Sort results using ORDER BY on columns, aliases, and positions.
Use row limiting (FETCH / LIMIT) for “Top‑N” style reports.
Add flexibility with substitution variables (& / &&) and MySQL user variables.

1. The WHERE Clause: Because “everyone” is rarely the right answer

Previously, you wrote queries like:

SELECT employee_id,
           last_name,
           job_id,
           department_id
    FROM   employees;

That returns every employee. But if you only want employees in department 90, you add a filter:

SELECT employee_id,
           last_name,
           job_id,
           department_id
    FROM   employees
    WHERE  department_id = 90;

Key points:

WHERE comes after FROM.
The condition must evaluate to TRUE for a row to be returned.
Character and date literals must be in single quotes:

  WHERE last_name = 'Whalen'
      WHERE hire_date = DATE '2015-01-01'

Default date display formats:

Oracle: DD-MON-RR (with the RR year format doing “rounded century” magic).
MySQL: YYYY-MM-DD.

Also: data values are often case‑sensitive. If Whalen is stored as Whalen (InitCap), searching for 'whalen' may not match, depending on collation. SQL keywords can be lazy about case; your data cannot.

2. Comparison Operators: Making the database pick a side

You can filter on:

= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> or != not equal

Examples:

-- Salary less than or equal to 3000
    SELECT last_name,
           salary
    FROM   employees
    WHERE  salary <= 3000;
    
    -- Find a specific last name
    SELECT last_name,
           salary
    FROM   employees
    WHERE  last_name = 'Abel';

So yes, this is where you start drawing arbitrary salary lines in the sand.

3. Ranges with BETWEEN (and NOT BETWEEN)

To filter within a range, use BETWEEN:

SELECT last_name,
           salary
    FROM   employees
    WHERE  salary BETWEEN 10000 AND 17000;

Important:

BETWEEN is inclusive – it includes 10000 and 17000.
The lower bound must be on the left, upper bound on the right.

To exclude the range, use NOT:

SELECT last_name,
           salary
    FROM   employees
    WHERE  salary NOT BETWEEN 10000 AND 17000;

Same idea in Oracle and MySQL; the math is equally unforgiving in both.

4. Lists with IN (and NOT IN)

If you want rows that match one of several values, you can either chain ORs like it’s 1995…

WHERE department_id = 20 OR department_id = 90

…or you can use IN:

WHERE department_id IN (20, 90);
    
    -- More values? Just add them
    WHERE department_id IN (20, 50, 90);

To get everyone except those departments:

WHERE department_id NOT IN (20, 50, 90);

The database understands “in this group” more gracefully than most people do.

5. Pattern Matching with LIKE, Wildcards, and ESCAPE

Sometimes you don’t know exactly what you’re looking for, just the shape of it. That’s LIKE.

Wildcards:

% – zero or more characters.
_ – exactly one character.

Examples:

-- Last names starting with capital H
    WHERE last_name LIKE 'H%';
    
    -- Last names containing capital H anywhere
    WHERE last_name LIKE '%H%';
    
    -- Last names where the third character is 'n'
    WHERE last_name LIKE '__n%';

Now, what if the data itself contains an underscore or percent sign, like job IDs SA_REP?

By default, _ and % are wildcards. To treat them as literal characters, use the ESCAPE clause:

WHERE job_id LIKE 'SA\_%' ESCAPE '\';

Here:

\_ means “actual underscore” instead of “any single character.”
% still acts as “zero or more characters.”

The pattern tools are powerful. They’re also how you accidentally discover everyone named “King” when you weren’t emotionally ready for that.

6. Testing for NULL

To find rows with or without values, use IS NULL and IS NOT NULL.

Example – employees without a manager:

SELECT last_name,
           manager_id
    FROM   employees
    WHERE  manager_id IS NULL;

This usually gives you the top of the org chart (e.g., King) – the person who reports to no one.

Employees with a manager:

WHERE manager_id IS NOT NULL;

Never use = NULL or <> NULL. SQL will quietly evaluate those to unknown, and you’ll get no rows and lots of confusion.

7. Logical Operators: AND, OR, NOT (and how they actually behave)

You can combine conditions using:

AND – returns TRUE only if both conditions are TRUE.
OR – returns TRUE if either condition is TRUE.
NOT – reverses the result.

Examples:

-- Salary = 10000 OR department = 90
    SELECT last_name,
           salary,
           department_id
    FROM   employees
    WHERE  salary = 10000
       OR  department_id = 90;

This returns anyone making 10000 plus anyone in department 90.

-- Salary = 24000 AND department = 90
    WHERE salary = 24000
      AND department_id = 90;

This returns employees who meet both conditions.

NOT flips truth:

-- Employees who DO have a manager
    WHERE NOT manager_id IS NULL;
    
    -- Employees whose job_id is none of these
    WHERE job_id NOT IN ('AD_PRES', 'AD_VP', 'AD_ASST');

7.1 Operator precedence and parentheses

Precedence (highest to lowest):

NOT
AND
OR

So this:

WHERE salary >= 10000
      AND job_id LIKE '%MAN%';

…does what you expect. But once you mix AND and OR, it gets tricky.

Example 1:

WHERE department_id = 80
      AND salary > 10000
       OR department_id = 60;

Interpreted as:

WHERE (department_id = 80 AND salary > 10000)
       OR department_id = 60;

Example 2 (with parentheses):

WHERE (department_id IN (60, 80))
      AND salary > 10000;

Completely different result set.

Moral: when mixing AND and OR, always use parentheses, unless you enjoy subtle, production‑grade bugs.

8. Sorting with ORDER BY

By default, queries return rows in whatever order the database finds convenient—often “deeply unhelpful.” Use ORDER BY to fix that.

Syntax:

SELECT last_name,
           manager_id,
           department_id
    FROM   employees
    ORDER BY manager_id;

Default sort order is ascending (ASC).
You can specify explicitly:

  ORDER BY manager_id ASC;
      ORDER BY manager_id DESC;

Using multiple sort keys:

ORDER BY department_id ASC,
             manager_id   DESC;

First sorted by department.
Within each department, managers are sorted highest to lowest.

You can also sort by column position (less readable, but works):

-- Sort by the 2nd column, then 3rd
    ORDER BY 2 ASC,
             3 DESC;

Or by alias:

SELECT last_name AS lname,
           salary
    FROM   employees
    ORDER BY lname;

If you used double quotes in the alias ("LName"), you must use the same case and quotes in ORDER BY.

NULLs typically sort:

Last for ascending order.
First for descending order.

(Exact behavior can vary by DB and settings.)

9. Row Limiting: Top‑N and Pagination

Sometimes you only want the top few rows—highest salaries, latest hires, etc.

9.1 Oracle row limiting with FETCH

Example – top 5 salaries:

SELECT last_name,
           salary
    FROM   employees
    ORDER BY salary DESC
    FETCH FIRST 5 ROWS ONLY;

With ties:

FETCH FIRST 2 ROWS WITH TIES;

If the 2nd and 3rd rows have the same salary, both are returned.

Pagination with offset:

ORDER BY salary DESC
    OFFSET 5 ROWS
    FETCH NEXT 5 ROWS ONLY;

Skips the first 5 rows and returns the next 5.

You can also limit by percentage:

FETCH FIRST 5 PERCENT ROWS ONLY;

On a 107‑row table, 5% ≈ 6 rows.

9.2 MySQL LIMIT

MySQL uses LIMIT instead:

-- First 7 rows
    SELECT last_name, salary
    FROM   employees
    ORDER BY salary DESC
    LIMIT 7;
    
    -- Skip first 5, return next 7
    SELECT last_name, salary
    FROM   employees
    ORDER BY salary DESC
    LIMIT 7 OFFSET 5;

Same idea, different syntax.

10. Substitution Variables (Oracle): Making Queries Ask Questions

Sometimes you don’t want to hard‑code a value; you want the user to supply it at runtime. Enter substitution variables.

They start with:

&variable_name – prompt the user each time it’s encountered.
&&variable_name – prompt once, then reuse the value for the rest of the session.

Example – prompt for department:

SELECT last_name,
           salary,
           department_id
    FROM   employees
    WHERE  department_id = &dept_id;

When executed, SQL Developer/SQL*Plus prompts:

> Enter value for dept_id:

Type 90, and the query runs with WHERE department_id = 90.

10.1 Single vs double ampersand

Using the same variable twice:

SELECT last_name,
           &column_name
    FROM   employees
    ORDER BY &column_name;

With a single &, you’ll be prompted twice.

With && on the first occurrence:

SELECT last_name,
           &&column_name
    FROM   employees
    ORDER BY &column_name;

Prompted once.
The value is remembered for the rest of the session.

To clear it:

UNDEFINE column_name;

10.2 Quotes for character and date input

If your variable is used where a string or date is expected, surround the variable with single quotes:

WHERE last_name = '&last_name';
    
    WHERE hire_date = DATE '&hire_date';

Numbers do not need quotes.

10.3 VERIFY and ECHO

In SQL*Plus / script-style output you can:

SET VERIFY ON – shows the original statement and the version with substituted values.
SET ECHO ON – prints each command before its output.

Both are useful for figuring out what actually ran, especially when multiple variables are involved.

11. MySQL User Variables

MySQL doesn’t use & substitution; it uses user-defined variables with @.

Example:

SET @employee_num = 200;
    
    SELECT last_name,
           salary
    FROM   employees
    WHERE  employee_id = @employee_num;

You assign them with SET or in queries, and refer to them using @variable_name.

12. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

Limit rows with WHERE using comparison, range, list, pattern, and NULL conditions.
Combine conditions safely with AND, OR, and NOT, using parentheses to control precedence.
Sort results using ORDER BY with columns, aliases, and positions.
Use row limiting (FETCH in Oracle, LIMIT in MySQL) for Top‑N and pagination.
Use substitution variables (&, &&, DEFINE, UNDEFINE) in Oracle to make queries interactive.
Use MySQL user variables to parameterize conditions.

You now have enough control to ask the database exactly who you want and exactly how you want them sorted—which, to be clear, is both extremely useful and exactly how bad dashboards are born.