How to Query and Filter Data in MySQL Using SELECT, WHERE, ORDER BY, DISTINCT, and LIMIT

Once data has been stored in MySQL tables, SQL queries allow you to retrieve exactly the information you need. Instead of displaying every record, you can select specific columns, filter records based on conditions, sort results, remove duplicate values, and limit the number of rows returned.

Learning Objectives

• Retrieve data using SELECT

• Select specific columns from a table

• Filter records using WHERE

• Combine conditions using AND and OR

• Filter multiple values using IN and NOT IN

• Find values within a range using BETWEEN

• Search text patterns using LIKE

• Sort query results using ORDER BY

• Remove duplicate results using DISTINCT

• Limit the number of returned rows

• Find NULL values

• Use table and column aliases

Creating Sample Data

For the examples in this article, we'll use the Products table created previously.

Example data:

If required, you can create fresh practice data using:

USE trainingdbc;

CREATE TABLE QueryProducts (

    product_id INT AUTO_INCREMENT PRIMARY KEY,

    product_name VARCHAR(100),

    category VARCHAR(50),

    price DECIMAL(10,2),

    stock INT

);

INSERT INTO QueryProducts (product_name, category, price, stock)

VALUES

('Laptop', 'Electronics', 3500.00, 10),

('Keyboard', 'Accessories', 150.00, 25),

('Mouse', 'Accessories', 75.00, 40),

('Monitor', 'Electronics', 850.00, 15),

('Printer', 'Electronics', 650.00, 8);

We'll use QueryProducts in the examples below so your existing Products table is not affected.

Retrieving Data Using SELECT

The SELECT statement retrieves information from a table.

To display every column:

SELECT * FROM QueryProducts;

The * tells MySQL to return all columns.

Selecting Specific Columns

You don't have to retrieve every column.

For example:

SELECT product_name, price

FROM QueryProducts;

This returns only the product name and price.

Filtering Data Using WHERE

The WHERE clause filters records based on a condition.

For example:

SELECT *

FROM QueryProducts

WHERE category = 'Electronics';

The result contains only products belonging to the Electronics category.

You can also filter numeric values:

SELECT *

FROM QueryProducts

WHERE price > 500;

This returns products priced above 500.

Using AND

AND requires all specified conditions to be true.

SELECT *

FROM QueryProducts

WHERE category = 'Electronics'

AND price > 700;

Based on our sample data, this returns:

• Laptop

• Monitor

Using OR

OR returns a record when at least one of the specified conditions is true.

SELECT *

FROM QueryProducts

WHERE category = 'Accessories'

OR price > 1000;

This can return accessory products along with products priced above 1000.

Using IN

IN checks whether a value matches any value in a specified list.

SELECT *

FROM QueryProducts

WHERE product_name IN ('Laptop', 'Mouse', 'Printer');

The result returns those three products.

Using NOT IN

NOT IN excludes specified values.

SELECT *

FROM QueryProducts

WHERE category NOT IN ('Accessories');

This returns products whose category is not Accessories.

Using BETWEEN

BETWEEN finds values within an inclusive range.

SELECT *

FROM QueryProducts

WHERE price BETWEEN 100 AND 1000;

With our sample data, this returns:

• Keyboard

• Monitor

• Printer

Searching with LIKE

LIKE searches for text patterns.

The % wildcard represents zero or more characters.

For example:

SELECT *

FROM QueryProducts

WHERE product_name LIKE 'M%';

This finds product names beginning with M, such as:

• Mouse

• Monitor

Another example:

SELECT *

FROM QueryProducts

WHERE product_name LIKE '%er';

This finds product names ending in er, such as Printer.

Sorting Results Using ORDER BY

ORDER BY sorts query results.

To sort by price from lowest to highest:

SELECT *

FROM QueryProducts

ORDER BY price ASC;

ASC means ascending order.

To display the most expensive products first:

SELECT *

FROM QueryProducts

ORDER BY price DESC;

DESC means descending order.

The descending result would begin with:

Laptop

Monitor

Printer

Keyboard

Mouse

Removing Duplicate Values Using DISTINCT

Suppose several products belong to the same category.

Running:

SELECT category

FROM QueryProducts;

can return the same category multiple times.

Use DISTINCT:

SELECT DISTINCT category

FROM QueryProducts;

The result should contain:

Electronics

Accessories

Limiting Results Using LIMIT

LIMIT controls how many rows MySQL returns.

For example:

SELECT *

FROM QueryProducts

LIMIT 3;

Only three records are returned.

LIMIT is particularly useful when working with large tables or previewing query results.

You can combine it with sorting:

SELECT *

FROM QueryProducts

ORDER BY price DESC

LIMIT 3;

This returns the three most expensive products.

Finding NULL Values Using IS NULL

NULL represents a missing or unknown value.

Suppose a product doesn't have a category assigned.

To find those records:

SELECT *

FROM QueryProducts

WHERE category IS NULL;

Use IS NULL rather than:

WHERE category = NULL

To find records that contain a value:

SELECT *

FROM QueryProducts

WHERE category IS NOT NULL;

Table and Column Aliases

Aliases provide temporary names for tables or columns within a query.

Column Alias

For example:

SELECT

    product_name AS Product,

    price AS Price

FROM QueryProducts;

The result headings appear as:

Product

Price

Table Alias

You can also give the table a shorter name:

SELECT

    p.product_name,

    p.category,

    p.price

FROM QueryProducts AS p;

Here, p is an alias for QueryProducts.

Aliases are especially useful when queries involve multiple tables and joins.

Combining Query Conditions

MySQL allows several querying techniques to be combined.

For example:

SELECT product_name, category, price

FROM QueryProducts

WHERE category = 'Electronics'

AND price >= 500

ORDER BY price DESC

LIMIT 2;

This query:

1. Selects product name, category, and price.

2. Filters Electronics products.

3. Keeps products priced at 500 or above.

4. Sorts them from highest to lowest price.

5. Returns only the first two records.

The result would be:

Common Mistakes

• Forgetting the FROM clause

• Using a column name that doesn't exist

• Forgetting quotation marks around string values

• Using = NULL instead of IS NULL

• Using AND when OR is required, or vice versa

• Forgetting that BETWEEN includes both boundary values

• Using LIKE without the appropriate wildcard

• Sorting in the wrong direction with ASC or DESC

• Applying LIMIT before considering how the results should be sorted