SQL Filtering and Sorting — ORDER BY, LIMIT, LIKE, IN, BETWEEN, NULL

All the SQL lessons from here use the same small company database. Run this once (in any SQL tool) and follow along.

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(30));
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR'), (40, 'Marketing');
CREATE TABLE employees (
    emp_id INT PRIMARY KEY, name VARCHAR(30), dept_id INT, salary INT,
    city VARCHAR(20), manager_id INT, joined DATE
);
INSERT INTO employees VALUES
 (1, 'Asha', 10, 95000, 'Jaipur', NULL, '2019-04-01'),
 (2, 'Ravi', 10, 72000, 'Pune', 1, '2021-07-15'),
 (3, 'Neha', 20, 58000, 'Jaipur', 1, '2020-01-10'),
 (4, 'Kabir', 20, 58000, 'Delhi', 3, '2022-03-01'),
 (5, 'Om', 30, 45000, 'Pune', 1, '2023-06-20'),
 (6, 'Meera', 10, 88000, 'Bengaluru', 1, '2018-11-05'),
 (7, 'Arjun', NULL, 40000, 'Delhi', 3, '2024-02-12');

1. ORDER BY and LIMIT

SELECT name, salary FROM employees ORDER BY salary DESC, name ASC LIMIT 3;

name | salary
Asha | 95000
Meera | 88000
Ravi | 72000

• ASC (default) small → big; DESC big → small.
• Several columns: the second breaks ties in the first.
• LIMIT n keeps the first n rows (SQL Server uses TOP n, Oracle FETCH FIRST n ROWS ONLY).

2. DISTINCT

SELECT DISTINCT city FROM employees ORDER BY city;

city
Bengaluru
Delhi
Jaipur
Pune

3. LIKE patterns

% = any number of characters, _ = exactly one character.

SELECT name FROM employees WHERE name LIKE '%a' ORDER BY name;

name
Asha
Meera
Neha

• 'A%' starts with A · '%ir%' contains "ir" · '_a%' second letter is a.

4. IN and BETWEEN

SELECT name, city, salary FROM employees
WHERE city IN ('Pune', 'Delhi') AND salary BETWEEN 40000 AND 60000
ORDER BY salary;

name | city | salary
Arjun | Delhi | 40000
Om | Pune | 45000
Kabir | Delhi | 58000

BETWEEN a AND b includes both ends.

5. NULL needs IS NULL

NULL means "unknown / no value". Any comparison with NULL using = is unknown, so it never matches.

SELECT name FROM employees WHERE dept_id IS NULL;

name
Arjun

WHERE dept_id = NULL would return nothing. Use IS NULL / IS NOT NULL, and COALESCE(col, default) to replace NULLs.

6. Aliases and calculated columns

SELECT name AS employee, salary * 12 AS yearly_ctc
FROM employees WHERE dept_id = 10 ORDER BY yearly_ctc DESC;

employee | yearly_ctc
Asha | 1140000
Meera | 1056000
Ravi | 864000

7. CASE: if-else inside SQL

SELECT name,
       CASE WHEN salary >= 80000 THEN 'Senior'
            WHEN salary >= 50000 THEN 'Mid'
            ELSE 'Junior' END AS band
FROM employees ORDER BY emp_id;

name | band
Asha | Senior
Ravi | Mid
Neha | Mid
Kabir | Mid
Om | Junior
Meera | Senior
Arjun | Junior

8. Handy functions

(Function names vary slightly between databases — e.g. MySQL also has CONCAT(), PostgreSQL uses || for joining text.)

Common mistakes

• WHERE col = NULL instead of IS NULL.
• Forgetting that LIKE is case-sensitive in some databases (PostgreSQL); use ILIKE or LOWER().
• Mixing AND and OR without brackets: WHERE a OR b AND c means a OR (b AND c).

Quick recap

• ORDER BY … ASC/DESC, LIMIT n, DISTINCT.
• LIKE with % and _; IN (list); BETWEEN (inclusive).
• NULL: IS NULL / IS NOT NULL / COALESCE.
• Aliases with AS; CASE WHEN for if-else.