SQL Select – Statement and Query Examples

Structured Query Language (SQL) is a programming language that you use to manage data in relational databases. You can use SQL to create, read, update, and delete (CRUD) data in a relational database.

You can write SQL queries to insert data with INSERT, read data with SELECT, update with UPDATE, and delete data with DELETE.

This article will teach you how to write SQL SELECT queries. You'll learn the various ways you can write these queries, and what the expected results should be.

How to Use the SQL SELECT Statement

You can use the SQL SELECT statement to retrieve data from a database table that has been specified.

You can write your statement in various ways to get the exact data you want. These data are extracted from the database table and returned as a table.

// Syntax

SELECT expression(s)
FROM table(s)
[WHERE condition(s)]
[ORDER BY expression(s) [ ASC | DESC ]];

The preceding code is a very detailed syntax that encompasses a lot of information that I'll explain with examples.

Let's begin by going over the parameters and arguments:

• expression(s): This represents the column(s) whose data you want to retrieve or the entire table's columns using an asterisk (*).
• table(s): The name of the table(s) from which you want to retrieve records. The FROM clause must include at least one table.
• WHERE condition(s): This is an optional field. This allows you to specify a condition that will guide the data that is retrieved for the specified column(s). If no conditions are specified, all records will be chosen.
• ORDER BY expression(s): This is an optional field. This allows you to declare a column whose data will be used to sort the results. A comma should separate the values if you provide more than one expression.
• ASC: This is an optional expression value. ASC sorts the result set by expression in ascending order. If this is not specified, this is the default behavior.
• DESC: This is an optional expression value. DESC sorts the result set by expression in descending order.

SQL Select Queries

Suppose you have a database with the name "Users" and has the following data:

Let's now explore various queries and see how they work.

SQL Select All

You might need to select all the columns from a database. Instead of listing each column, you can use the asterisk (*) character.

SELECT *
FROM Users;

Here is what your output will look like when you make use of this command on the user's table:

SQL Select Specified Columns

You can also fetch specified columns instead of all the columns by listing the columns and separating them by a comma:

SELECT first_name, last_name
FROM Users;

Here is what your output will look like when you make use of this command on the user's table:

SQL Select WHERE Clause

You may want to return only rows that satisfy a specific condition. This condition can be specified using the optional WHERE clause. The WHERE clause allows you to retrieve records from a database table that match a given condition(s).

For example, suppose you only want to fetch users whose status is "single":

SELECT *
FROM Users
WHERE status = 'Single';

Here is what your output will look like when you make use of this command on the user's table:

Generally, the WHERE clause is used to filter the results. You can also use common operators like =, which you used, and others like <, >, <=, >=, AND, BETWEEN, and IN.

SELECT Using Equality Operators

Suppose you want to fetch only users whose age is greater than 30. Then your query will be:

SELECT *
FROM Users
WHERE age > 30;

Here is what your output will look like when you make use of this command on the user's table:

You can also use other equality operators like <, <=, and >=.

SELECT Using the AND Operator

You might often want to use more than one condition to filter the table's contents. You can do this with the AND operator.

SELECT *
FROM Users
WHERE status = 'Single' AND age > 30;

Here is what your output will look like when you make use of this command on the user's table:

SELECT Using the BETWEEN Operator

You use the BETWEEN operator to get the range of data you want to filter. You can decide to use the equality and AND operator, but BETWEEN provides a better syntax.

SELECT *
FROM Users
WHERE age BETWEEN 20 AND 30;

Here is what your output will look like when you make use of this command on the user's table:

SELECT Using the IN Operator

Also, the IN operator lets you set more than one exact basis for filtering each row. For example, you can fetch only rows whose value is in the bracket defined:

SELECT *
FROM Users
WHERE age IN (56,33,10);

Here is what your output will look like when you make use of this command on the user's table:

SQL Select ORDER BY Clause

So far, you have learned how to fetch from your table with SQL, but you will notice that these data always follow the original order. You can adjust the order in which the data is fetched using the ORDER BY clause.

Two major options are Ascending (ASC) and descending (DESC) order. For example, you might want your table's rows to be arranged in ascending order based on the first_name:

SELECT *
FROM Users
ORDER BY first_name ASC;

Here is what your output will look like when you make use of this command on the user's table:

Note: You can always combine these options and clauses in one query to fetch exactly what you want.

Wrapping up!

In this article, you learned how to use the SQL SELECT query to retrieve data from a relational database. Other options are available, but these are the ones you'll most likely use regularly.

Have fun coding!