Now that you know the basic SQL commands, let’s learn some more SELECT techniques that make your queries more powerful.

Filtering with WHERE

The WHERE clause lets you find specific data. You’ve already used WHERE in UPDATE and DELETE commands - now we’ll use it with SELECT to filter your results.

SELECT * FROM users WHERE username = 'alice_smith';

This finds only the user(s) with username ‘alice_smith’. Since username is not unique in our table (unlike id), this query could potentially return multiple rows if there are multiple users with the same username.

Other comparison operators:

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE 'a%';
SELECT * FROM users WHERE username IS NOT NULL;
  • email LIKE '%@gmail.com' finds users with Gmail addresses (the % means “anything before”)
  • username LIKE 'a%' finds users whose username starts with ‘a’
  • username IS NOT NULL finds users who have a username set

Sorting with ORDER BY

Sort your results in a specific order:

SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users ORDER BY email ASC;
  • ORDER BY username sorts alphabetically by username
  • ORDER BY id DESC sorts by id, highest first, so newest users first

  • ORDER BY email ASC sorts by email, A-Z

  • ASC = ascending (A-Z, 1-10) - this is the default
  • DESC = descending (Z-A, 10-1)

Limiting Results with LIMIT

Show only a certain number of results:

SELECT * FROM users LIMIT 2;
SELECT * FROM users ORDER BY id DESC LIMIT 1;
  • LIMIT 2 shows only the first 2 users
  • ORDER BY id DESC LIMIT 1 shows only the newest user (highest id)

Combining Everything

You can combine WHERE, ORDER BY, and LIMIT:

SELECT username, email FROM users
WHERE email LIKE '%@example.com'
ORDER BY username ASC
LIMIT 2;

This query:

  • Finds users with example.com emails
  • Sorts them alphabetically by username
  • Shows only the first 2 results

Tags: