My Web Training

Retrieving Data with SELECT

To retrieve data, use the SELECT statement with a column list and the table name. The most basic version returns all rows and selected columns from a single table.

SELECT statement syntax

SELECT select_list
  FROM table_source
  [WHERE search_condition]
  [ORDER BY order_by_list]
  [LIMIT [offset,] row_limit];

Each SQL statement is made up of one or more clauses, which define the components of the operation. In a SELECT statement, common clauses include SELECT, FROM, WHERE, ORDER BY, and LIMIT. Each clause has a specific purpose and order within the statement.

Key Clauses

SELECT

Specifies the columns to return.

FROM

Indicates the table(s) to query.

WHERE

Filters rows based on conditions.

ORDER BY

Sorts the result set by specified columns.

LIMIT

Restricts the number of rows returned.

💡 Tip: When writing SELECT statements, it helps to think in this order: Select 'columns' From 'table'. Start by deciding what information you want (the columns), then where it comes from (the table). Once you have this, then you can work on any additional clauses like WHERE or ORDER BY to filter and sort the rows.

Basic example

SELECT LastName, FirstName FROM customer;
  -- Returns only two columns from the customer table
  
  SELECT * FROM customer;
  -- Returns all columns from the customer table

🔥 Pro Tip: Using * in the SELECT clause returns all columns from the table. This can be useful during testing or exploration, but it's better to list only the columns you need in production queries — especially when working with large datasets or joined tables.

Filtering Rows

To return only specific rows from a table, use the WHERE clause to define a filter condition. Conditions can be based on values, ranges, or logical relationships between multiple criteria.

Comparison Operators

These operators are commonly used in WHERE conditions to compare values.

WHERE clause

The WHERE clause applies a condition to limit which rows are returned. Only rows that meet the condition will be included in the result set.

SELECT * FROM invoice WHERE InvoiceId = 3;
-- Returns only the row with an invoice ID of 3

Logical Operators

Logical operators are used to combine or modify conditions in a WHERE clause.

Use AND and OR to combine multiple conditions. AND requires all conditions to be true; OR returns rows where at least one condition is true.

SELECT * FROM customer 
WHERE City = 'Paris' AND Credit_Limit < 5000.00;
-- Returns customers in Paris with a credit limit under $5000

SELECT * FROM customer 
WHERE City = 'Paris' OR Credit_Limit < 5000.00;
-- Returns customers in Paris OR any customer with a credit limit under $5000

NOT operator

The NOT operator negates a condition. It is logically equivalent to using the != or <> operators, though the syntax may read less naturally. For example, WHERE NOT State = 'CA' means the same as WHERE State != 'CA' — both exclude rows where the state is California.

SELECT * FROM customer 
WHERE NOT State = 'CA';

-- Same result:
SELECT * FROM customer 
WHERE State != 'CA';

Combining NOT with AND / OR

The NOT operator negates the condition that immediately follows it. When combined with AND and OR, use parentheses to ensure the logic behaves as intended. Without parentheses, SQL follows this precedence order:

• NOT is evaluated first
• Then AND
• Then OR

This can produce unexpected results unless grouped explicitly.

-- Example 1: NOT applies only to City = 'Paris'
SELECT * FROM customer 
WHERE NOT City = 'Paris' OR State = 'TX';
-- Returns customers NOT in Paris OR anyone in Texas
-- (even if they're in Paris)

-- Example 2: Parentheses negate the entire expression
SELECT * FROM customer 
WHERE NOT (City = 'Paris' OR State = 'TX');
-- Returns only customers who are not in Paris AND not in Texas

Use parentheses whenever negating a compound condition to make your intent clear and prevent logic errors.

IN operator

The IN operator checks whether a column value matches any item in a list. It is functionally equivalent to multiple OR conditions but is easier to read and maintain.

SELECT * FROM customer 
WHERE State IN ('CA', 'FL', 'WA');
-- Returns customers where State is CA, FL, or WA

This is equivalent to:

SELECT * FROM customer 
WHERE State = 'CA' OR State = 'FL' OR State = 'WA';

IN is especially useful when comparing against longer lists or values that may change over time (e.g., region codes, product categories, or user roles).

🔥 Pro Tip: The list inside an IN (...) clause doesn't have to be hard-coded — it can come from another query. This is useful for filtering based on related data from another table.

BETWEEN operator

The BETWEEN operator filters values within a range. It checks whether a column value is greater than or equal to the lower bound and less than or equal to the upper bound.

SELECT * FROM invoice 
WHERE Total BETWEEN 5 AND 10;
-- Returns invoices with totals from $5 to $10, inclusive

BETWEEN is inclusive on both ends. The query above is functionally the same as:

SELECT * FROM invoice 
WHERE Total >= 5 AND Total <= 10;

Use BETWEEN to simplify numeric or date range comparisons in filters.

Sorting and Limiting Results

To organize or reduce the results returned, use the ORDER BY, LIMIT, and OFFSET clauses.

ORDER BY clause

The ORDER BY clause sorts the result set based on one or more columns. By default, sorting is in ascending order (ASC), but you can explicitly reverse the order using DESC for descending.

SELECT * FROM customer 
ORDER BY LastName;
-- Sorts customers A–Z by last name

SELECT * FROM customer 
ORDER BY LastName DESC;
-- Sorts customers Z–A by last name

Sorting by multiple columns

You can sort by more than one column. The second column acts as a tie-breaker when two or more rows have the same value in the first sort column.

SELECT * FROM customer 
ORDER BY State, LastName;
-- Sorts by state, then by last name within each state

💡 Tip: The ORDER BY clause only changes the order of the query results — it does not affect the actual order of rows stored in the table.

LIMIT clause

The LIMIT clause restricts how many rows are returned in the result set. This is useful when reviewing a sample of data or when only the top results are needed.

SELECT * FROM invoice 
ORDER BY Total DESC 
LIMIT 5;
-- Returns the top 5 invoices with the highest total

💡 Tip: The first row has an offset of 0. For example, LIMIT 5 returns the first five rows without skipping any.

OFFSET with LIMIT

An optional OFFSET value can be added to skip a number of rows before starting to return results. When used with LIMIT, the format is LIMIT offset, row_count.

SELECT * FROM invoice 
ORDER BY InvoiceId 
LIMIT 2, 3;
-- Skips the first 2 rows and returns the next 3

💡 Tip: MySQL's two-argument syntax LIMIT offset, count can be confusing — the offset comes first, not second. For example, LIMIT 2, 3 skips the first two rows (OFFSET) and returns the next three (LIMIT).

Summary and Tips

• Start simple: SELECT columns FROM table
• Add WHERE to filter, ORDER BY to sort, and LIMIT to reduce output
• Use logical operators (AND, OR, NOT) to refine conditions
• Use IN and BETWEEN to simplify list and range comparisons

When troubleshooting a query, remove clauses and rebuild step by step. Confirm each part returns the expected result before adding complexity.