This article contains examples of basic SQL queries that beginners can use to retrieve data from their databases.
Basic SELECT Query
Here’s an example of possibly, the most commonly used query in SQL:
SELECT *
FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
This query selects all rows and all columns from the Pets table. This is because the asterisk (*) wildcard selects all columns.
Select Column Names
For performance reasons, it’s usually best to avoid selecting all columns unless you really need them. It’s usually better to select just the columns you need.
Here’s an example.
SELECT PetId, PetName
FROM Pets;Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 1 | Fluffy | | 2 | Fetch | | 3 | Scratch | | 4 | Wag | | 5 | Tweet | | 6 | Fluffy | | 7 | Bark | | 8 | Meow | +---------+-----------+
Filter the Results
You can add a WHERE clause to filter the results to just the rows you need.
SELECT PetId, PetName
FROM Pets
WHERE PetName = 'Fluffy';Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 1 | Fluffy | | 6 | Fluffy | +---------+-----------+
Here’s another example of filtering the results. This time we use the greater than operator (>) to filter it by date.
SELECT PetName, DOB
FROM Pets
WHERE DOB > '2020-01-01';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fluffy | 2020-11-20 | | Wag | 2020-03-15 | | Tweet | 2020-11-28 | | Fluffy | 2020-09-17 | +-----------+------------+
You can swap the greater than operator to other operators, such as the greater than or equals to operator (>=), less than operator (<), or less than or equals to operator (<=).
You can also use the BETWEEN operator to filter the results to a specific range (e.g. between two dates).
SELECT
PetName,
DOB
FROM Pets
WHERE DOB BETWEEN '2018-01-01' AND '2020-01-01';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | +-----------+------------+
Sort the Results
You can add an ORDER BY clause to sort the rows that are returned by the query.
Ascending Order
Use the ASC keyword to sort the results in ascending order. This is the default value, so you can also omit this keyword if you want the results in ascending order.
SELECT PetId, PetName
FROM Pets
ORDER BY PetName ASC;Or:
SELECT PetId, PetName
FROM Pets
ORDER BY PetName;Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 7 | Bark | | 2 | Fetch | | 1 | Fluffy | | 6 | Fluffy | | 8 | Meow | | 3 | Scratch | | 5 | Tweet | | 4 | Wag | +---------+-----------+
Descending Order
Use the DESC keyword to sort the results in descending order.
SELECT PetId, PetName
FROM Pets
ORDER BY PetName DESC;Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 4 | Wag | | 5 | Tweet | | 3 | Scratch | | 8 | Meow | | 1 | Fluffy | | 6 | Fluffy | | 2 | Fetch | | 7 | Bark | +---------+-----------+
Sort by Multiple Columns
You can sort by multiple columns by listing each column, separated by a comma.
SELECT PetId, PetName
FROM Pets
ORDER BY PetName ASC, PetId ASC;
SELECT PetId, PetName
FROM Pets
ORDER BY PetName ASC, PetId DESC;Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 7 | Bark | | 2 | Fetch | | 1 | Fluffy | | 6 | Fluffy | | 8 | Meow | | 3 | Scratch | | 5 | Tweet | | 4 | Wag | +---------+-----------+ (8 rows affected) +---------+-----------+ | PetId | PetName | |---------+-----------| | 7 | Bark | | 2 | Fetch | | 6 | Fluffy | | 1 | Fluffy | | 8 | Meow | | 3 | Scratch | | 5 | Tweet | | 4 | Wag | +---------+-----------+ (8 rows affected)
We can see that the two Fluffys are a different order in each result (we can tell by looking at their PetId values). This is because the PetName column was sorted first, then the PetId sorted any duplicates from the first sorting.
Sort by Hidden Columns
You can sort by columns that aren’t included in the SELECT list.
SELECT PetId, PetName
FROM Pets
ORDER BY DOB DESC;Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 5 | Tweet | | 1 | Fluffy | | 6 | Fluffy | | 4 | Wag | | 2 | Fetch | | 3 | Scratch | | 7 | Bark | | 8 | Meow | +---------+-----------+
In this case, we can infer from these results that Tweet is the youngest pet, and Meow is the oldest. This is because we sorted by their date of birth (DOB) column in descending order.
Just to be sure, here it is again with the DOB column included in the SELECT list.
SELECT PetId, PetName, DOB
FROM Pets
ORDER BY DOB DESC;Result:
+---------+-----------+------------+ | PetId | PetName | DOB | |---------+-----------+------------| | 5 | Tweet | 2020-11-28 | | 1 | Fluffy | 2020-11-20 | | 6 | Fluffy | 2020-09-17 | | 4 | Wag | 2020-03-15 | | 2 | Fetch | 2019-08-16 | | 3 | Scratch | 2018-10-01 | | 7 | Bark | NULL | | 8 | Meow | NULL | +---------+-----------+------------+
Actually, we can now see that Meow and Bark have NULL values in the DOB column. Therefore, we don’t know if they’re actually older or younger.
But this demonstrates that NULL values are treated as the lowest possible values. Be mindful of NULL values when running queries.
Pattern Matching
You can use the LIKE operator to use pattern matching.
SELECT PetId, PetName
FROM Pets
WHERE PetName LIKE 'F%';Result:
+---------+-----------+ | PetId | PetName | |---------+-----------| | 1 | Fluffy | | 2 | Fetch | | 6 | Fluffy | +---------+-----------+
In this example, we search for all pets whose names start with the letter F. The percentage sign (%) is a wildcard character that matches any string of zero or more characters. It can be used as either a prefix or a suffix, and it can also be used in the middle of a string.
Here’s another example.
SELECT FirstName, LastName, Email
FROM Owners
WHERE Email LIKE '%@example.%';Result:
+-------------+------------+-------------------+ | FirstName | LastName | Email | |-------------+------------+-------------------| | Homer | Connery | [email protected] | | Bart | Pitt | [email protected] | +-------------+------------+-------------------+
Select from a List
The IN operator determines whether a specified value matches any value in a subquery or a list.
Here’s an example.
SELECT
PetId,
PetName,
DOB
FROM Pets
WHERE PetName IN ('Fluffy', 'Bark', 'Wag');Result:
+---------+-----------+------------+ | PetId | PetName | DOB | |---------+-----------+------------| | 1 | Fluffy | 2020-11-20 | | 4 | Wag | 2020-03-15 | | 6 | Fluffy | 2020-09-17 | | 7 | Bark | NULL | +---------+-----------+------------+
Subqueries
You can use the IN operator when performing a subquery (a query nested inside another query).
Here’s an example.
SELECT
PetTypeId,
PetType
FROM PetTypes
WHERE PetTypeId IN ( SELECT PetTypeId FROM Pets );Result:
+-------------+-----------+ | PetTypeId | PetType | |-------------+-----------| | 1 | Bird | | 2 | Cat | | 3 | Dog | +-------------+-----------+
This returned columns from one table (PetTypes), but only when there was at least one corresponding row in another table (Pets) that had a matching the PetTypeId column.
To demonstrate this further, the relevant contents of these two tables is shown below.
The PetTypes table:
+-------------+-----------+ | PetTypeId | PetType | |-------------+-----------| | 1 | Bird | | 2 | Cat | | 3 | Dog | | 4 | Rabbit | +-------------+-----------+
The Pets table:
+-------------+-----------+ | PetTypeId | PetName | |-------------+-----------| | 2 | Fluffy | | 3 | Fetch | | 2 | Scratch | | 3 | Wag | | 1 | Tweet | | 3 | Fluffy | | 3 | Bark | | 2 | Meow | +-------------+-----------+
We can see that the PetTypes table contains a pet type of Rabbit, but none of the pets in the Pets table have been assigned that type (i.e. there’s no value of 4 in the Pets.PetTypeId column).
See 12 Commonly Used SQL Operators and this list of SQL Operators for more information about operators in SQL.
Joins
It’s arguable whether SQL joins are considered “basic SQL queries”, but I’ll include a join here anyway.
So to round off this article, here’s an example of an inner join.
SELECT
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;Result:
+-----------+-----------+ | PetName | PetType | |-----------+-----------| | Fluffy | Cat | | Fetch | Dog | | Scratch | Cat | | Wag | Dog | | Tweet | Bird | | Fluffy | Dog | | Bark | Dog | | Meow | Cat | +-----------+-----------+
In this case, we used an INNER JOIN to return all pet names with their respective pet types. We used the ON clause to specify the predicate to be evaluated for each pair of joined rows. In this case, the p.PetTypeId column is a foreign key of the pt.PetTypeId column, which is the primary key for the PetTypes table.
In this example, I also used aliases on the tables, which helped to keep the code nice and concise.
See my SQL Joins Tutorial for more examples of joins.