SELECT statement in SQL

A SELECT statement, also known as a query, is perhaps the most popular and prominent DML command when working with databases. SELECT allows us to consult the data of a table.

The basic syntax is very simple:

SELECT column1, column2... FROM tableName

SELECT statements are usually accompanied by the WHERE clause after the FROM, which allows you to add filters to the query to restrict or filter the records to return. There are also other clauses after the WHERE that affect how the records will be displayed and the order in which they will be displayed, such as GROUP BY and ORDER BY.

Additionally, SELECT allows you to return fields from multiple independent but related tables through a Foreign Key via the JOIN clause.

Depending on the database model and the specific data we want to return, a SELECT can be a very simple statement of just one line, or a very complex one with many table crossings and specific filters that we must take into account to return the required information.

In this section, we will try to explain and exemplify the use of SELECT, taking as an example the Customers and Orders tables that we are using in the other content sections.

To illustrate this section, we have previously performed several INSERT INTO on the Customers and Orders tables. The reader is free to insert the same ones for his tests or those that fit his model.

SELECT * FROM

SELECT * FROM is the most basic statement for querying a given table. * tells us that we are going to return all the fields of the corresponding table.

SELECT *
FROM Customers;

Note that the query returns all the records and columns of the Customers table.

SELECT [COLUMN] FROM

A SELECT allows you to specify which columns of the corresponding table/s will be displayed after execution.

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders o;

We observe how in this SELECT we have named the table Orders with an alias o.

WHERE clause

The WHERE clause allows us to add filters to our queries to return only the records that meet the specified conditions from the entire result set:

SELECT [name],
       lastName
FROM Customers
WHERE customerId = 2;

We return only the record specified in the customerId.

SELECT product,
       price,
       productQuantity,
       totalPrice
FROM Orders
WHERE ordersDate = '2022-09-01'

In this example, we have filtered the OrderDate field that matches two records from the Orders table.