SQL SELECT and WHERE: Querying Data the Right Way

SQL is the language of data. Whether you are building a backend API, analyzing business metrics, or debugging a production issue, knowing how to write precise SELECT queries is one of the most valuable skills you can develop. In this article you will learn everything about retrieving data: selecting all columns, selecting specific columns, renaming them with aliases, and filtering rows with the WHERE clause and all its operators.

We will use a realistic e-commerce database throughout so every example feels grounded in something you might actually build.

The E-Commerce Database We Will Use

Before writing any queries, here is the schema we will work with. All examples in this article use these three tables.

SQL

-- Products catalog
CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    name         VARCHAR(200)  NOT NULL,
    category     VARCHAR(100)  NOT NULL,
    price        DECIMAL(10,2) NOT NULL,
    stock        INT           NOT NULL DEFAULT 0,
    is_active    BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at   DATE          NOT NULL
);

-- Registered customers
CREATE TABLE customers (
    customer_id  INT           PRIMARY KEY,
    first_name   VARCHAR(100)  NOT NULL,
    last_name    VARCHAR(100)  NOT NULL,
    email        VARCHAR(200)  UNIQUE NOT NULL,
    city         VARCHAR(100),
    country      VARCHAR(100)  NOT NULL DEFAULT 'US',
    signup_date  DATE          NOT NULL
);

-- Orders placed by customers
CREATE TABLE orders (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL REFERENCES customers(customer_id),
    product_id   INT           NOT NULL REFERENCES products(product_id),
    quantity     INT           NOT NULL,
    unit_price   DECIMAL(10,2) NOT NULL,
    status       VARCHAR(50)   NOT NULL,  -- 'pending', 'shipped', 'delivered', 'cancelled'
    order_date   DATE          NOT NULL
);

Sample data so you can follow along:

SQL

INSERT INTO products VALUES
(1,  'Wireless Headphones',  'Electronics',   89.99,  120, TRUE,  '2024-01-10'),
(2,  'USB-C Hub',            'Electronics',   34.99,  250, TRUE,  '2024-01-15'),
(3,  'Mechanical Keyboard',  'Electronics',  129.99,   80, TRUE,  '2024-02-01'),
(4,  'Standing Desk Mat',    'Office',        49.99,   60, TRUE,  '2024-02-20'),
(5,  'Notebook (A5)',        'Stationery',     8.99,  500, TRUE,  '2024-03-01'),
(6,  'Ballpoint Pen Set',    'Stationery',     5.99,  800, TRUE,  '2024-03-05'),
(7,  'Monitor Stand',        'Office',        39.99,   45, FALSE, '2024-03-10'),
(8,  'Webcam HD',            'Electronics',   69.99,   30, TRUE,  '2024-03-15'),
(9,  'Laptop Bag',           'Accessories',   59.99,   90, TRUE,  '2024-04-01'),
(10, 'Screen Cleaner Kit',   'Accessories',   12.99,  200, TRUE,  '2024-04-05');

INSERT INTO customers VALUES
(1,  'Alice',   'Johnson',  'alice@example.com',    'New York',    'US', '2023-06-01'),
(2,  'Bob',     'Smith',    'bob@example.com',       'Los Angeles', 'US', '2023-07-15'),
(3,  'Carol',   'White',    'carol@example.com',     'London',      'GB', '2023-08-20'),
(4,  'David',   'Brown',    'david@example.com',     'Toronto',     'CA', '2023-09-10'),
(5,  'Eve',     'Davis',    'eve@example.com',       NULL,          'US', '2023-10-05'),
(6,  'Frank',   'Wilson',   'frank@example.com',     'Sydney',      'AU', '2024-01-12'),
(7,  'Grace',   'Moore',    'grace@example.com',     'New York',    'US', '2024-02-28'),
(8,  'Henry',   'Taylor',   'henry@example.com',     'Chicago',     'US', '2024-03-14');

INSERT INTO orders VALUES
(1,  1, 1,  2, 89.99,  'delivered', '2024-03-01'),
(2,  1, 3,  1, 129.99, 'delivered', '2024-03-10'),
(3,  2, 2,  1, 34.99,  'shipped',   '2024-04-01'),
(4,  2, 8,  1, 69.99,  'pending',   '2024-04-10'),
(5,  3, 5, 10,  8.99,  'delivered', '2024-02-15'),
(6,  3, 6, 20,  5.99,  'delivered', '2024-02-20'),
(7,  4, 9,  1, 59.99,  'shipped',   '2024-04-05'),
(8,  5, 4,  1, 49.99,  'cancelled', '2024-03-20'),
(9,  7, 1,  1, 89.99,  'delivered', '2024-04-12'),
(10, 8, 10, 3, 12.99,  'pending',   '2024-04-14');

SELECT *: Fetching Every Column

The simplest form of a SELECT statement retrieves every column from a table using the asterisk wildcard.

SQL

SELECT * FROM products;

This returns all 10 rows with every column: product_id, name, category, price, stock, is_active, created_at.

When SELECT * is acceptable:

Exploratory queries in a database client where you are just looking around
Quick debugging during development
Ad-hoc investigations on small tables

When SELECT * is a problem:

In application code — if someone adds a column later your app may break or transfer unnecessary data
Over large tables with wide rows — you are fetching columns you never use
In views or CTEs that are reused elsewhere

As a rule: write column names explicitly in code that ships to production.

SELECT Specific Columns

Name the columns you want, separated by commas.

SQL

SELECT product_id, name, price
FROM products;

Result:

| product_id | name | price | |------------|----------------------|--------| | 1 | Wireless Headphones | 89.99 | | 2 | USB-C Hub | 34.99 | | 3 | Mechanical Keyboard | 129.99 | | ... | ... | ... |

You can reorder columns in the output simply by listing them in the order you want:

SQL

SELECT price, name, product_id
FROM products;

The underlying table is not changed. You are only choosing what the result set looks like.

Column Aliases with AS

An alias renames a column in the output. This is useful when column names are technical or verbose, when you are computing an expression, or when building reports.

SQL

SELECT
    product_id   AS id,
    name         AS product_name,
    price        AS unit_price_usd
FROM products;

The AS keyword is optional — you can write price unit_price_usd — but using AS makes the intent explicit and is strongly recommended for readability.

Aliases on expressions:

SQL

SELECT
    name,
    price,
    price * 1.10   AS price_with_tax,
    price * 0.90   AS discounted_price
FROM products;

| name | price | price_with_tax | discounted_price | |---------------------|--------|----------------|-----------------| | Wireless Headphones | 89.99 | 98.989 | 80.991 | | USB-C Hub | 34.99 | 38.489 | 31.491 |

Aliases with spaces: if your alias needs spaces, wrap it in double quotes:

SQL

SELECT
    name            AS "Product Name",
    price           AS "Price (USD)"
FROM products;

Note: some databases use backticks (MySQL) or square brackets (SQL Server) instead of double quotes. PostgreSQL uses double quotes.

Can you use aliases in WHERE? No. Aliases defined in SELECT are not visible to WHERE because the database evaluates WHERE before SELECT. This is a common mistake:

SQL

-- WRONG: this fails
SELECT price * 1.10 AS price_with_tax
FROM products
WHERE price_with_tax > 100;

-- CORRECT: repeat the expression
SELECT price * 1.10 AS price_with_tax
FROM products
WHERE price * 1.10 > 100;

The WHERE Clause

WHERE filters which rows come back. Only rows where the condition evaluates to TRUE are included in the result.

SQL

SELECT *
FROM products
WHERE category = 'Electronics';

Returns the 4 active electronics products (and the 1 inactive one — we will filter that shortly).

The WHERE clause comes after FROM and before ORDER BY, GROUP BY, and HAVING.

SQL

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

Comparison Operators

SQL gives you six basic comparison operators.

Equal: =

SQL

SELECT * FROM products WHERE category = 'Office';

Returns Standing Desk Mat and Monitor Stand (both in the Office category).

Not Equal: != or <>

Both mean "not equal". != is more common in modern SQL; <> is ANSI standard.

SQL

SELECT name, category FROM products WHERE category != 'Electronics';

Returns everything that is not Electronics: Office, Stationery, Accessories.

Less Than and Greater Than: < > <= >=

SQL

-- Products under $40
SELECT name, price FROM products WHERE price < 40.00;

-- Products $40 and above
SELECT name, price FROM products WHERE price >= 40.00;

-- Products costing exactly $49.99 to $89.99 (inclusive)
SELECT name, price FROM products WHERE price >= 49.99 AND price <= 89.99;

These work on numbers, dates, and strings (strings compare alphabetically).

SQL

-- Customers who signed up on or after 2024-01-01
SELECT first_name, last_name, signup_date
FROM customers
WHERE signup_date >= '2024-01-01';

BETWEEN

BETWEEN is shorthand for >= lower AND <= upper. Both endpoints are inclusive.

SQL

-- Products priced between $30 and $70 (inclusive)
SELECT name, price
FROM products
WHERE price BETWEEN 30.00 AND 70.00;

This is equivalent to:

SQL

WHERE price >= 30.00 AND price <= 70.00

BETWEEN works on dates too:

SQL

-- Orders placed in April 2024
SELECT order_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2024-04-01' AND '2024-04-30';

NOT BETWEEN excludes the range:

SQL

-- Products outside the $30-$70 range
SELECT name, price
FROM products
WHERE price NOT BETWEEN 30.00 AND 70.00;

IN

IN lets you test against a list of values. It is cleaner than writing multiple OR conditions.

SQL

-- Products in Electronics or Accessories
SELECT name, category
FROM products
WHERE category IN ('Electronics', 'Accessories');

Compare to the verbose equivalent:

SQL

WHERE category = 'Electronics' OR category = 'Accessories'

IN with numbers:

SQL

-- Specific orders by ID
SELECT order_id, status, order_date
FROM orders
WHERE order_id IN (1, 3, 5, 7);

NOT IN excludes listed values:

SQL

-- Everything except Office and Stationery
SELECT name, category
FROM products
WHERE category NOT IN ('Office', 'Stationery');

Caution with NOT IN and NULLs: if the list contains a NULL, NOT IN returns no rows at all. This is a subtle SQL trap covered in the NULL section below.

IN with a subquery (a preview of things to come):

SQL

-- Customers who have placed at least one order
SELECT first_name, last_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

LIKE: Pattern Matching

LIKE lets you filter strings by pattern. There are two wildcard characters:

| Wildcard | Meaning | |----------|-------------------------------| | % | Zero or more characters | | _ | Exactly one character |

Starts with a pattern

SQL

-- Products whose name starts with 'W'
SELECT name FROM products WHERE name LIKE 'W%';
-- Returns: Wireless Headphones, Webcam HD

Ends with a pattern

SQL

-- Products whose name ends with 'Kit'
SELECT name FROM products WHERE name LIKE '%Kit';
-- Returns: Screen Cleaner Kit

Contains a pattern

SQL

-- Products with 'stand' anywhere in the name (case-sensitive in most DBs)
SELECT name FROM products WHERE name LIKE '%Stand%';
-- Returns: Standing Desk Mat, Monitor Stand

Single character wildcard

SQL

-- Customers with a 5-letter first name starting with 'A'
SELECT first_name FROM customers WHERE first_name LIKE 'A____';
-- Returns: Alice

Case sensitivity

In PostgreSQL, LIKE is case-sensitive. Use ILIKE for case-insensitive matching:

SQL

-- PostgreSQL only
SELECT name FROM products WHERE name ILIKE '%keyboard%';

In SQL Server, case sensitivity depends on the collation. Most default collations are case-insensitive, so LIKE 'keyboard%' and LIKE 'Keyboard%' return the same results.

NOT LIKE

SQL

-- Products not in the 'Stand...' family of names
SELECT name FROM products WHERE name NOT LIKE '%Stand%';

Escaping wildcards

If you need to search for a literal % or _, use the ESCAPE clause:

SQL

-- Find products where the name literally contains '20%'
SELECT name FROM products WHERE name LIKE '%20!%%' ESCAPE '!';

IS NULL and IS NOT NULL

NULL in SQL means "unknown" or "missing". It is not zero, it is not an empty string. It is the absence of a value.

Because NULL represents the unknown, you cannot compare it with = or !=:

SQL

-- WRONG: this returns no rows even if city is NULL
SELECT * FROM customers WHERE city = NULL;

-- CORRECT
SELECT * FROM customers WHERE city IS NULL;

This is the single most common NULL mistake beginners make. Always use IS NULL or IS NOT NULL.

SQL

-- Customers with no city on file
SELECT first_name, last_name, email
FROM customers
WHERE city IS NULL;
-- Returns: Eve Davis (city is NULL)

-- Customers who do have a city
SELECT first_name, last_name, city
FROM customers
WHERE city IS NOT NULL;
-- Returns everyone except Eve

NULL in expressions: any arithmetic with NULL produces NULL:

SQL

SELECT 5 + NULL;      -- NULL
SELECT NULL * 100;    -- NULL
SELECT NULL = NULL;   -- NULL (not TRUE!)

This is why NOT IN with a NULL in the list is dangerous:

SQL

-- If the subquery returns NULL among its values, NOT IN returns nothing
SELECT * FROM customers
WHERE customer_id NOT IN (1, 2, NULL);
-- Returns 0 rows because NOT IN with NULL is always unknown

AND, OR, NOT

Combine multiple conditions with logical operators.

AND: both conditions must be true

SQL

-- Active Electronics products priced under $100
SELECT name, price, is_active
FROM products
WHERE category = 'Electronics'
  AND is_active = TRUE
  AND price < 100.00;

Returns: Wireless Headphones (89.99), USB-C Hub (34.99), Webcam HD (69.99).

OR: at least one condition must be true

SQL

-- Products in Stationery OR priced under $15
SELECT name, category, price
FROM products
WHERE category = 'Stationery'
   OR price < 15.00;

Returns: Notebook, Ballpoint Pen Set, Screen Cleaner Kit (because it is under $15).

NOT: negates a condition

SQL

-- Products that are NOT active
SELECT name, is_active
FROM products
WHERE NOT is_active;

-- Equivalent to:
WHERE is_active = FALSE
WHERE is_active != TRUE

Combining all three

SQL

-- Orders that are either delivered, or were placed before April 2024 and are not cancelled
SELECT order_id, status, order_date
FROM orders
WHERE status = 'delivered'
   OR (order_date < '2024-04-01' AND status != 'cancelled');

Operator Precedence

This is where many developers introduce subtle bugs. SQL evaluates operators in a specific order, and AND has higher precedence than OR. This means AND binds tighter.

Consider this query:

SQL

SELECT name, category, price
FROM products
WHERE category = 'Electronics'
   OR category = 'Office'
  AND price > 40.00;

What you might intend: "(Electronics OR Office) AND price > 40"

What SQL actually does: "Electronics OR (Office AND price > 40)"

Because AND is evaluated first, this returns ALL Electronics products (regardless of price) PLUS only Office products over $40.

The fix: use parentheses explicitly.

SQL

-- This is what you probably meant
SELECT name, category, price
FROM products
WHERE (category = 'Electronics' OR category = 'Office')
  AND price > 40.00;

Now it means: must be in Electronics or Office, AND must be over $40.

Precedence order (highest to lowest):

NOT
AND
OR

Another example that trips people up:

SQL

-- Intended: active products that are NOT in Electronics or Office
-- Written incorrectly:
WHERE is_active = TRUE AND NOT category = 'Electronics' OR category = 'Office'

-- What it actually means:
WHERE (is_active = TRUE AND (NOT category = 'Electronics')) OR category = 'Office'
-- Which includes ALL Office products regardless of is_active!

-- Correct version:
WHERE is_active = TRUE AND category NOT IN ('Electronics', 'Office')
-- or
WHERE is_active = TRUE AND NOT (category = 'Electronics' OR category = 'Office')

Rule of thumb: whenever you mix AND and OR, use parentheses even if you think you know the precedence. It makes the query readable and bug-free.

Practical Query Examples

Let us put everything together with some realistic business queries.

1. Active products available for purchase

SQL

SELECT
    product_id,
    name         AS product_name,
    category,
    price        AS unit_price,
    stock        AS units_in_stock
FROM products
WHERE is_active = TRUE
  AND stock > 0
ORDER BY category, price;

2. Mid-range electronics

SQL

SELECT
    name,
    price
FROM products
WHERE category = 'Electronics'
  AND price BETWEEN 50.00 AND 100.00
  AND is_active = TRUE;

Returns: Wireless Headphones (89.99), Webcam HD (69.99).

3. UK or Australian customers

SQL

SELECT
    first_name,
    last_name,
    email,
    country
FROM customers
WHERE country IN ('GB', 'AU');

Returns: Carol White (GB), Frank Wilson (AU).

4. Customers with Gmail addresses

SQL

SELECT
    first_name,
    last_name,
    email
FROM customers
WHERE email LIKE '%@gmail.com';
-- Returns 0 rows with our sample data — all use @example.com
-- But the pattern is correct for real data

5. Orders that need attention (pending or shipped but not recently placed)

SQL

SELECT
    order_id,
    customer_id,
    status,
    order_date
FROM orders
WHERE status IN ('pending', 'shipped')
  AND order_date < '2024-04-10';

6. Customers with no city listed who signed up in 2023

SQL

SELECT
    first_name,
    last_name,
    email,
    signup_date
FROM customers
WHERE city IS NULL
  AND signup_date BETWEEN '2023-01-01' AND '2023-12-31';

Returns: Eve Davis.

7. Products with suspicious names (contain special patterns for data quality audit)

SQL

-- Find products where name contains numbers (data quality check)
SELECT name FROM products WHERE name LIKE '%[0-9]%';   -- SQL Server syntax
SELECT name FROM products WHERE name ~ '[0-9]';        -- PostgreSQL syntax

ORDER BY: Sorting Results

Technically ORDER BY is not part of WHERE, but you will use it on almost every SELECT. It sorts the result set.

SQL

-- Products sorted by price ascending (cheapest first)
SELECT name, price
FROM products
ORDER BY price ASC;

-- Products sorted by price descending (most expensive first)
SELECT name, price
FROM products
ORDER BY price DESC;

Sort by multiple columns:

SQL

-- Sort by category, then within each category sort by price
SELECT category, name, price
FROM products
ORDER BY category ASC, price DESC;

You can sort by a column that is not in your SELECT list:

SQL

SELECT name, price
FROM products
ORDER BY created_at DESC;

You can also use the column position number (but avoid this in production code — fragile if columns move):

SQL

SELECT name, category, price
FROM products
ORDER BY 3 DESC;  -- sorts by price (3rd column)

LIMIT and TOP: Restricting Row Count

PostgreSQL / MySQL

SQL

-- Top 5 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Skip first 5, get next 5 (pagination)
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

SQL Server

SQL

-- Top 5 most expensive products
SELECT TOP 5 name, price
FROM products
ORDER BY price DESC;

-- Pagination with OFFSET FETCH (SQL Server 2012+)
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

DISTINCT: Removing Duplicates

DISTINCT removes duplicate rows from the result.

SQL

-- What categories do we have?
SELECT DISTINCT category
FROM products;

Returns: Electronics, Office, Stationery, Accessories (4 unique values instead of 10 rows).

DISTINCT applies to the combination of all selected columns:

SQL

-- Unique country + city combinations
SELECT DISTINCT country, city
FROM customers
WHERE city IS NOT NULL;

Common Mistakes and How to Avoid Them

Mistake 1: Using = with NULL

SQL

-- WRONG
WHERE city = NULL      -- always returns 0 rows

-- RIGHT
WHERE city IS NULL

Mistake 2: Forgetting precedence with AND/OR

SQL

-- Ambiguous (AND binds tighter)
WHERE a = 1 OR b = 2 AND c = 3

-- Clear intent
WHERE a = 1 OR (b = 2 AND c = 3)

Mistake 3: NOT IN with possible NULLs

SQL

-- Dangerous if subquery can return NULL
WHERE id NOT IN (SELECT some_column FROM some_table)

-- Safer alternative
WHERE NOT EXISTS (
    SELECT 1 FROM some_table
    WHERE some_table.some_column = outer_table.id
)

Mistake 4: LIKE with no wildcard behaves like equals

SQL

WHERE name LIKE 'Keyboard'   -- same as WHERE name = 'Keyboard'
-- If you want "starts with Keyboard", write:
WHERE name LIKE 'Keyboard%'

Mistake 5: Comparing strings and numbers without thinking about type

SQL

-- If product_id is an INT, do not wrap in quotes
WHERE product_id = 1       -- correct
WHERE product_id = '1'     -- works in many DBs but causes implicit cast, potentially slow

Quick Reference

| Operator | Example | Meaning | |-----------------|--------------------------------------|----------------------------------| | = | price = 49.99 | Equal | | != or <> | status != 'cancelled' | Not equal | | lt (<) | price < 50 | Less than | | gt (>) | stock > 0 | Greater than | | lte (<=) | price <= 100 | Less than or equal | | gte (>=) | signup_date >= '2024-01-01' | Greater than or equal | | BETWEEN | price BETWEEN 30 AND 70 | Inclusive range | | IN | country IN ('US', 'GB') | Match any value in list | | LIKE | email LIKE '%@gmail.com' | Pattern match (% = any, _ = one) | | IS NULL | city IS NULL | Value is missing | | IS NOT NULL | city IS NOT NULL | Value is present | | AND | active = TRUE AND price < 100 | Both must be true | | OR | country = 'US' OR country = 'GB' | Either must be true | | NOT | NOT is_active | Negation |

Key Takeaways

SELECT * is fine for exploration but always name columns explicitly in production code.
Aliases (AS) rename columns in the output and can be applied to expressions; they are not available in WHERE.
WHERE filters rows before they are returned; only rows where the condition is TRUE come through.
BETWEEN low AND high is inclusive on both ends.
IN (list) is cleaner than many OR conditions; avoid NOT IN when the list might contain NULL.
LIKE '%pattern%' uses % for any-length wildcards and _ for single-character wildcards.
IS NULL and IS NOT NULL are the only correct ways to test for NULL — never use = NULL.
AND has higher precedence than OR. Use parentheses to make complex conditions explicit.
Combine WHERE with ORDER BY and LIMIT/TOP to build precise, paginated result sets.

In the next article we cover INSERT, UPDATE, DELETE, and how transactions keep your data safe when things go wrong.