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:

Syntax
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.

Example

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.

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
customerId name lastName address bankAccount
1 Janice Haynes 4822 Radio Park Drive 111222333
2 Mavin Pettitt 2336 Cottonwood Lane 123456789
3 Peter Davis 3608 Sycamore Lake Road 998344567
4 Helen Ward 711 Hershell Hollow Road 447824556
5 Kimberly Lee 4298 Drummond Street 778345112

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

It is very likely that, on many occasions, we do not need to return all the information in a table, just some specific columns of the entire result set. It will always be better to return only the required columns.

SELECT [COLUMN] FROM

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

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
product price productQuantity
Xiaomi Mi 11 286,95 15
Play Station 5 549,95 4
Xbox series X 499,99 2
MacBook Pro M1 2449,5 1
Echo DOT 3 39,99 50
Echo DOT 4 59,99 50
Nintendo Switch 299,99 3

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

The use of aliases will make it easier for us to access the fields of the tables when writing the statements.

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:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name lastName
Mavin Pettitt

We return only the record specified in the customerId.

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
product price productQuantity totalPrice
Echo DOT 3 39,99 50 1999,5
Echo DOT 4 59,99 50 2999,5

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

WHERE allows concatenating conditions to apply more filters to the query through the AND operator. Different operators may also be used to help filter and restrict records, which may be consulted from the following access link to the list of operators.