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

Source: https://skills101.in/learn/dbms-sql/sql-filtering-and-sorting — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

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

![The departments and employees tables are just like this students table: rows, columns and a primary key](https://skills101.in/learn-img/dbms-sql/table-anatomy.svg)

```sql
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

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

```text
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

```sql
SELECT DISTINCT city FROM employees ORDER BY city;
```

```text
city
Bengaluru
Delhi
Jaipur
Pune
```

## 3. LIKE patterns

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

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

```text
name
Asha
Meera
Neha
```

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

## 4. IN and BETWEEN

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

```text
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.

```sql
SELECT name FROM employees WHERE dept_id IS NULL;
```

```text
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

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

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

## 7. CASE: if-else inside SQL

```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;
```

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

## 8. Handy functions

| Function | Example | Result |
|---|---|---|
| `UPPER(x)`, `LOWER(x)` | `UPPER('asha')` | ASHA |
| `LENGTH(x)` | `LENGTH('Kabir')` | 5 |
| `SUBSTR(x, start, len)` | `SUBSTR('Jaipur', 1, 3)` | Jai |
| `ROUND(x, d)` | `ROUND(7.456, 1)` | 7.5 |
| `COALESCE(a, b)` | `COALESCE(NULL, 'none')` | none |

(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.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-filtering-and-sorting