Using SELECT with WHERE, LIMIT, ORDER BY, and OFFSET

SQL provides powerful tools to filter, sort, and paginate data from your database using SELECT queries. In this lesson, you’ll learn how to construct SELECT statements using the WHERE, LIMIT, ORDER BY, and OFFSET clauses to efficiently retrieve and manipulate data.

WHERE Clause

The WHERE clause filters rows in a table based on specified conditions. Let’s explore its usage in different scenarios.

WHERE Clause for a Single Value

To retrieve rows that match a single value, use the syntax:

SELECT [columns] 
  FROM [table] 
  WHERE [column] = 'value';

Example:

Retrieve all puppies that are Miniature Schnauzers:

SELECT name, breed 
  FROM puppies 
  WHERE breed = 'Miniature Schnauzer';

Result:

name     breed
Cooper   Miniature Schnauzer
Leinni   Miniature Schnauzer
    

WHERE Clause for a List of Values

Use the IN operator to filter rows that match a list of values:

SELECT [columns] 
  FROM [table] 
  WHERE [column] IN ('value1', 'value2', 'value3');

Example:

Find all puppies that are Corgis, Beagles, or Yorkshire Terriers:

SELECT name, breed 
  FROM puppies 
  WHERE breed IN ('Corgi', 'Beagle', 'Yorkshire Terrier');

Result:

name     breed
Indie    Yorkshire Terrier
Callie   Corgi
Jaxson   Beagle
    

WHERE Clause for a Range of Values

Use the BETWEEN operator to filter rows within a numeric range:

SELECT [columns] 
  FROM [table] 
  WHERE [column] BETWEEN value1 AND value2;

Example:

Find puppies between 0 to 6 months old:

SELECT name, breed, age_yrs 
  FROM puppies 
  WHERE age_yrs BETWEEN 0 AND 0.5;

Result:

name     breed             age_yrs
Indie    Yorkshire Terrier  0.5
Jaxson   Beagle             0.4
    

ORDER BY Clause

Use ORDER BY to sort query results:

SELECT [columns] 
  FROM [table] 
  ORDER BY [column] [ASC|DESC];

Examples:

• Sort by name in ascending order:

  SELECT name, breed 
    FROM puppies 
    ORDER BY name;

• Sort by age in descending order:

  SELECT name, breed 
    FROM puppies 
    ORDER BY age_yrs DESC;

LIMIT and OFFSET Clauses

Use LIMIT to restrict the number of rows returned. Combine it with OFFSET to paginate results.

SELECT [columns] 
  FROM [table] 
  ORDER BY [column] 
  LIMIT [number] OFFSET [number];

Examples:

• Retrieve the 100 youngest puppies:

  SELECT name, breed 
    FROM puppies 
    ORDER BY age_yrs 
    LIMIT 100;

• Retrieve the next 100 puppies (rows 101-200):

  SELECT name, breed 
    FROM puppies 
    ORDER BY age_yrs 
    LIMIT 100 OFFSET 100;

Sample Data and Setup

To follow along, use the following SQL to create and populate the puppies table:

CREATE TABLE puppies (
  name VARCHAR(50),
  age_yrs NUMERIC(3,1),
  breed VARCHAR(100),
  weight_lbs INTEGER,
  microchipped BOOLEAN
);

INSERT INTO puppies
VALUES
  ('Cooper', 1, 'Miniature Schnauzer', 18, 1),
  ('Indie', 0.5, 'Yorkshire Terrier', 13, 1),
  ('Kota', 0.7, 'Australian Shepherd', 26, 0),
  ('Zoe', 0.8, 'Korean Jindo', 32, 1),
  ('Charley', 1.5, 'Basset Hound', 25, 0),
  ('Ladybird', 0.6, 'Labradoodle', 20, 1),
  ('Callie', 0.9, 'Corgi', 16, 0),
  ('Jaxson', 0.4, 'Beagle', 19, 1),
  ('Leinni', 1, 'Miniature Schnauzer', 25, 1),
  ('Max', 1.6, 'German Shepherd', 65, 0);
    

What You Learned

In this lesson, you explored:

• Using the WHERE clause to filter data by single values, lists, and ranges.
• Sorting query results with ORDER BY.
• Restricting query results with LIMIT and paginating results with OFFSET.

Real-World Applications

These techniques are crucial for applications such as:

• E-commerce: Filtering and sorting products by category, price, or popularity.
• Content platforms: Paginating articles or videos for users to browse.
• Data analysis: Extracting subsets of data for reporting or visualization.

By mastering these clauses, you can efficiently query and manipulate data in any relational database.