The SELECT statement is almost without doubt, the most commonly used statement in SQL.
The SELECT statement is used for retrieving data from the database. You can specify which rows you want returned, and which columns.
Example
Here’s an example to demonstrate the SELECT statement.
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 | +---------+-------------+-----------+-----------+------------+
In this example we used the asterisk (*) wildcard to return all columns. We also used the FROM clause to specify which table to get data from.
So this query returns all rows and all columns from the Pets table.
Specify the Columns
You can specify the columns you’d like to have returned.
To do this, replace the asterisk wildcard with the column names, each separated by a comma, in the order you’d like them to be returned.
SELECT PetId, PetName, DOB
FROM Pets;Result:
+---------+-----------+------------+ | PetId | PetName | DOB | |---------+-----------+------------| | 1 | Fluffy | 2020-11-20 | | 2 | Fetch | 2019-08-16 | | 3 | Scratch | 2018-10-01 | | 4 | Wag | 2020-03-15 | | 5 | Tweet | 2020-11-28 | | 6 | Fluffy | 2020-09-17 | | 7 | Bark | NULL | | 8 | Meow | NULL | +---------+-----------+------------+
It is generally considered good practice to do it this way, and return only those columns that you actually need. Using the asterisk wildcard to return all columns can have an adverse impact on performance, especially in larger databases.
Specify the Rows
There are many ways to filter the results to just those rows you’re interested in. The most common way is to use the WHERE clause. This clause allows you to specify a condition that a row must satisfy in order to qualify.
Here’s an example to demonstrate.
SELECT PetId, PetName, DOB
FROM Pets
WHERE PetName = 'Fluffy';Result:
+---------+-----------+------------+ | PetId | PetName | DOB | |---------+-----------+------------| | 1 | Fluffy | 2020-11-20 | | 6 | Fluffy | 2020-09-17 | +---------+-----------+------------+
In our case, there were two rows that satisfied our search condition.
Our search condition uses the equals operator (=) to specify that the value of the PetName column must match the specified value (Fluffy) exactly.
There are many other operators that you can use in your search conditions. For example, you could use the LIKE operator to return pets who’s name starts with F or Fluff or whatever.
You can add more conditions to the WHERE clause by using operators such as the AND and OR operators.
Here’s an example.
SELECT PetId, PetName, DOB
FROM Pets
WHERE PetName = 'Fluffy'
OR DOB < '2020-01-01';Result:
+---------+-----------+------------+ | PetId | PetName | DOB | |---------+-----------+------------| | 1 | Fluffy | 2020-11-20 | | 2 | Fetch | 2019-08-16 | | 3 | Scratch | 2018-10-01 | | 6 | Fluffy | 2020-09-17 | +---------+-----------+------------+
In this example we returned all rows where the pet’s name is Fluffy or where the pet’s date of birth (specified in the DOB column) is less than a certain date. We used the less than operator (<) to specify that.
Returning Non-Table Data
The SELECT statement can also be used to return data that isn’t stored in a table. For example, it’s perfectly legal to do this:
SELECT 'Hey there!';Result:
+--------------------+ | (No column name) | |--------------------| | Hey there! | +--------------------+
Notice that when we do that, the column has no name. We could use an alias to give a name to the resulting field.
Here are a few more examples of selecting non-table data, and providing an alias to each field returned:
SELECT
2 + 3 AS "2 + 3",
'Fluffy' + ' ' + 'Smith' AS FullName,
SYSDATETIME() AS "Date/time";Result:
+---------+--------------+-----------------------------+ | 2 + 3 | FullName | Date/time | |---------+--------------+-----------------------------| | 5 | Fluffy Smith | 2020-12-04 23:58:55.8483734 | +---------+--------------+-----------------------------+
Here’s a quick rundown:
- The first column simply added two numbers.
- The second column concatenated three strings (including a space character). You can also concatenate strings with numbers if required.
- The third column used the
SYSDATETIME()function to return the current date and time. This function is available in SQL Server. Other DBMSs have their own date and time functions. See SQLite Date/Time Functions, SQL Server Date/Time Functions, and PostgreSQL Date/Time Functions for a list of date & time functions available in those DBMSs.
Subqueries
It’s possible to have multiple SELECT statements in a single query. We can do this using a subquery.
SELECT
PetTypeId,
PetType
FROM PetTypes
WHERE PetTypeId IN ( SELECT PetTypeId FROM Pets );Result:
+-------------+-----------+ | PetTypeId | PetType | |-------------+-----------| | 1 | Bird | | 2 | Cat | | 3 | Dog | +-------------+-----------+
In this example, we used the IN operator to specify a subquery. That subquery selected data from another table, and the IN operator related it back to the outer query.
Ordering the Results
You can use the ORDER BY clause to sort the results.
For example, we could sort the previous results by the PetTypeId column in descending order:
SELECT
PetTypeId,
PetType
FROM PetTypes
WHERE PetTypeId IN ( SELECT PetTypeId FROM Pets )
ORDER BY PetTypeId DESC;Result:
+-------------+-----------+ | PetTypeId | PetType | |-------------+-----------| | 3 | Dog | | 2 | Cat | | 1 | Bird | +-------------+-----------+
See SQL ORDER BY Clause for Beginners for more examples and a detailed explanation.
More Advanced SELECT Statements
The SELECT statement is a very simple statement, but it can be used to create very complex queries.
Here’s a query that’s slightly more complex than the previous ones.
SELECT
PetTypes.PetType,
COUNT(Pets.PetTypeId) AS Count
FROM Pets
LEFT JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId
GROUP BY PetTypes.PetType
ORDER BY Count DESC;Result:
+-----------+---------+ | PetType | Count | |-----------+---------| | Dog | 4 | | Cat | 3 | | Bird | 1 | +-----------+---------+
In the world of SQL queries, this is still pretty simple, however, it does contain many of the elements often seen in more complex queries.
For example, this query uses a join to retrieve data from multiple tables. It also uses the GROUP BY clause to divide the rows into groups.
See SQL GROUP BY Clause for Beginners for more examples of the GROUP BY clause.
See SQL Joins Tutorial to see how to retrieve data from multiple tables and return them as one result set.
The SELECT INTO Statement
Some DBMSs support the SELECT INTO statement. This is slightly different to the previous examples, in that it affects the data in the database.
The SELECT INTO statement creates a new table and inserts data into it from another table.
Basically, it goes like this:
SELECT * INTO Table2
FROM Table1;In this example, we create a new table called Table2, and insert all data from Table1 into it. Table2 will have the same definition as Table1.
See SQL SELECT INTO Statement for more examples.
As I alluded to, not all DBMSs support this statement. If your DBMS doesn’t support the SELECT INTO statement, try using the CREATE TABLE ... AS SELECT statement instead.
Those statements create a new table as part of their operation. To insert data into a table that already exists, try using the INSERT INTO ... SELECT statement instead.