SQL Where | Academic Block

SQL WHERE

Learn how to filter specific records from database tables using conditions with the SQL WHERE clause.

What is the WHERE Clause?

The WHERE clause is used to filter records in a SQL query. It returns only the rows that satisfy a specified condition.

You can use WHERE with comparison operators, logical operators, IN, BETWEEN, LIKE, and IS NULL.

Basic WHERE Syntax

The WHERE clause is normally placed after the FROM clause.

WITH books(title, author, price) AS (
  VALUES
    ('The Hidden Ocean', 'Lena Roy', 450),
    ('Digital World', 'Arun Das', 650),
    ('Beyond Mars', 'Nina Shah', 900)
)
SELECT title, price
FROM books
WHERE price > 500;

WHERE with Equal (=)

Use the = operator when you want an exact match.

WITH courses(course, level) AS (
  VALUES
    ('Physics', 'Beginner'),
    ('Chemistry', 'Intermediate'),
    ('Mathematics', 'Advanced'),
    ('Biology', 'Beginner')
)
SELECT course, level
FROM courses
WHERE level = 'Beginner';

WHERE with Not Equal (!=)

Use != to select records that do not match a particular value.

WITH vehicles(model, fuel) AS (
  VALUES
    ('Swift', 'Petrol'),
    ('Nexon', 'Diesel'),
    ('City', 'Petrol'),
    ('Kona', 'Electric')
)
SELECT model, fuel
FROM vehicles
WHERE fuel != 'Petrol';

WHERE with Greater Than (>)

Use > to select values greater than a specified number.

WITH flights(route, duration) AS (
  VALUES
    ('Delhi-Mumbai', 135),
    ('Delhi-Chennai', 165),
    ('Mumbai-Goa', 75),
    ('Delhi-Kolkata', 130)
)
SELECT route, duration
FROM flights
WHERE duration > 140;

WHERE with Less Than (<)

Use < to select values smaller than a specified number.

WITH sensors(sensor, temperature) AS (
  VALUES
    ('S-101', 28),
    ('S-102', 35),
    ('S-103', 42),
    ('S-104', 31)
)
SELECT sensor, temperature
FROM sensors
WHERE temperature < 35;

WHERE with Greater Than or Equal (>=)

The >= operator includes the specified value as well as larger values.

WITH players(name, score) AS (
  VALUES
    ('Kabir', 72),
    ('Ishita', 88),
    ('Dev', 65),
    ('Tara', 90)
)
SELECT name, score
FROM players
WHERE score >= 80;

WHERE with Less Than or Equal (<=)

The <= operator includes the specified value and smaller values.

WITH rooms(room, capacity) AS (
  VALUES
    ('A101', 20),
    ('A102', 35),
    ('B201', 50),
    ('B202', 25)
)
SELECT room, capacity
FROM rooms
WHERE capacity <= 25;

WHERE with AND

The AND operator returns a row only when all conditions are true.

WITH laptops(model, ram, price) AS (
  VALUES
    ('Nova 14', 8, 52000),
    ('ProBook X', 16, 78000),
    ('Air Lite', 8, 45000),
    ('Ultra 15', 16, 92000)
)
SELECT model, ram, price
FROM laptops
WHERE ram = 16
  AND price < 90000;

WHERE with OR

The OR operator returns a row when at least one condition is true.

WITH orders(order_id, status) AS (
  VALUES
    (101, 'Pending'),
    (102, 'Shipped'),
    (103, 'Cancelled'),
    (104, 'Delivered')
)
SELECT order_id, status
FROM orders
WHERE status = 'Pending'
   OR status = 'Shipped';

WHERE with NOT

The NOT operator reverses a condition.

WITH devices(device, status) AS (
  VALUES
    ('Router-A', 'Online'),
    ('Router-B', 'Offline'),
    ('Router-C', 'Online'),
    ('Router-D', 'Maintenance')
)
SELECT device, status
FROM devices
WHERE NOT status = 'Offline';

WHERE with IN

IN allows you to check a column against several possible values.

WITH cities(name, region) AS (
  VALUES
    ('Jaipur', 'North'),
    ('Pune', 'West'),
    ('Bhopal', 'Central'),
    ('Surat', 'West'),
    ('Patna', 'East')
)
SELECT name, region
FROM cities
WHERE region IN ('West', 'Central');

WHERE with NOT IN

NOT IN selects records whose values are not included in the specified list.

WITH fruits(name, color) AS (
  VALUES
    ('Apple', 'Red'),
    ('Banana', 'Yellow'),
    ('Grapes', 'Green'),
    ('Orange', 'Orange'),
    ('Kiwi', 'Green')
)
SELECT name, color
FROM fruits
WHERE color NOT IN ('Green', 'Yellow');

WHERE with BETWEEN

BETWEEN filters values within an inclusive range.

WITH games(title, rating) AS (
  VALUES
    ('Sky Quest', 6.5),
    ('Dark Planet', 8.2),
    ('Ocean Run', 7.4),
    ('Lost Valley', 9.1)
)
SELECT title, rating
FROM games
WHERE rating BETWEEN 7.0 AND 8.5;

WHERE with NOT BETWEEN

NOT BETWEEN returns values outside the specified range.

WITH temperatures(city, temp) AS (
  VALUES
    ('Delhi', 38),
    ('Shimla', 18),
    ('Mumbai', 31),
    ('Leh', 12)
)
SELECT city, temp
FROM temperatures
WHERE temp NOT BETWEEN 20 AND 35;

WHERE with LIKE

LIKE can be used to search for text matching a particular pattern.

WITH products(name) AS (
  VALUES
    ('Smart Watch'),
    ('Smartphone'),
    ('Wireless Mouse'),
    ('Bluetooth Speaker'),
    ('Smart Lamp')
)
SELECT name
FROM products
WHERE name LIKE 'Smart%';

WHERE with IS NULL

Use IS NULL to find records where a column has no value.

WITH deliveries(package, tracking_code) AS (
  VALUES
    ('Package A', 'TRK1001'),
    ('Package B', NULL),
    ('Package C', 'TRK1003'),
    ('Package D', NULL)
)
SELECT package, tracking_code
FROM deliveries
WHERE tracking_code IS NULL;

WHERE with IS NOT NULL

Use IS NOT NULL to find records that contain a value.

WITH deliveries(package, tracking_code) AS (
  VALUES
    ('Package A', 'TRK1001'),
    ('Package B', NULL),
    ('Package C', 'TRK1003'),
    ('Package D', NULL)
)
SELECT package, tracking_code
FROM deliveries
WHERE tracking_code IS NOT NULL;

WHERE with Multiple Conditions

Parentheses make complex combinations of AND and OR conditions easier to understand.

WITH courses(course, category, fee) AS (
  VALUES
    ('Robotics', 'Technology', 12000),
    ('Photography', 'Arts', 8000),
    ('Web Design', 'Technology', 10000),
    ('Music Production', 'Arts', 15000),
    ('Data Analysis', 'Technology', 18000)
)
SELECT course, category, fee
FROM courses
WHERE (category = 'Technology' AND fee < 15000)
   OR category = 'Arts';

WHERE with ORDER BY

WHERE filters the records and ORDER BY sorts the resulting rows.

WITH movies(title, year, rating) AS (
  VALUES
    ('Orbit', 2022, 7.8),
    ('The Signal', 2024, 8.6),
    ('Blue Horizon', 2021, 7.1),
    ('Final Mission', 2025, 9.0),
    ('Deep Space', 2023, 8.2)
)
SELECT title, year, rating
FROM movies
WHERE rating > 8.0
ORDER BY rating DESC;

WHERE with LIMIT

In SQLite, WHERE can be combined with ORDER BY and LIMIT to filter, sort, and restrict the number of results.

WITH products(name, price) AS (
  VALUES
    ('Camera', 42000),
    ('Tablet', 28000),
    ('Drone', 55000),
    ('Projector', 36000),
    ('Headphones', 7000)
)
SELECT name, price
FROM products
WHERE price > 20000
ORDER BY price DESC
LIMIT 3;

Common WHERE Operators

Operator	Purpose	Example
`=`	Equal to	`level = 'Beginner'`
`!=`	Not equal to	`fuel != 'Petrol'`
`>`	Greater than	`price > 500`
`<`	Less than	`temperature < 35`
`>=`	Greater than or equal to	`score >= 80`
`<=`	Less than or equal to	`capacity <= 25`
`IN`	Match multiple values	`region IN ('West','Central')`
`BETWEEN`	Match a range	`rating BETWEEN 7 AND 8`
`LIKE`	Match text patterns	`name LIKE 'Smart%'`
`IS NULL`	Find NULL values	`tracking_code IS NULL`

Advantages of WHERE

Filters unwanted records from query results.
Helps retrieve only the required data.
Supports numerical and text-based conditions.
Can combine multiple conditions.
Works with operators such as IN, BETWEEN, and LIKE.
Can be combined with ORDER BY and LIMIT.

Best Practices

Use WHERE to avoid retrieving unnecessary rows.
Use parentheses when combining complex AND and OR conditions.
Use IS NULL instead of = NULL.
Use IN when checking several possible values.
Use BETWEEN for inclusive ranges.
Use LIKE for pattern-based text searches.
Use indexed columns in filtering conditions when working with large tables.

The WHERE clause allows you to precisely filter database records. By combining WHERE with comparison and logical operators, you can retrieve exactly the information you need.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.