Query tables with SELECT

A simple request

The SELECT statement is used to query one or more rows of one or more tables.

A simple SELECT query looks like this:

SELECT {list of columns | *}
FROM table 
[WHERE conditional expression];

Columns can appear in any order. Using the (*) character returns all the columns of the table, in the order they exist in the table.

Let’s return all columns of table [products], for a specific product, with code ‘ABC‘.

SELECT *
FROM products
WHERE product_code = 'ABC';

The SELECT clause

The SELECT clause is not only used to return a list of columns. Any mathematical expression, function or value can be indicated in the statement instead of a column.

As example, the following query selects all rows from [products] table, showing for each the [product_code] and related price calculated in the same query.

SELECT product_code, (quantity * cost * 1.07), 'dollars'
FROM product;

For the sake of clarity, we can, for the benefit of a query, change the name of a column by adding an alias.

It is placed immediately after the name of a column, or a expression of columns (e.g. a calculation), and is separated by a space.

SELECT quantity * cost * 1.07 price
FROM product;

You can also use the optional keyword AS to express the definition of the alias.

SELECT quantity * cost * 1.07 AS price
FROM product;

Eliminate Duplicates

Sometimes, a query may display repetitive lines (at least in appearance), especially when the primary key is not part of the displayed columns.

These repetitions are eliminated by using the DISTINCT keyword in the SELECT clause at the beginning from the list of columns.

SELECT [DISTINCT] list of columns

Any row with the same values as another row already presented will be ignored.

Note: DISTINCT operates on the entire row and not on one column at a time.

Sorting results

To sort the results of a SELECT one must use the ORDER BY clause which must be placed at the very end of the instruction.

ORDER BY [list of columns]

By default, the sort will be ascending. It is possible to modify this criterion, column by column.

Just add the DESC keyword after the column you want to sort.

Example: List the names of employees in descending chronological order of birth dates.

SELECT name, birthdate
FROM employee
ORDER BY
birthdate DESC;

Note:

• The ORDER BY clause must be always the last part of the query.
• The column (s) used must also be present in the SELECT clause (some DBMS allow to use columns for sorting that are not present in the SELECT).
• Column names can be replaced by numbers that represent their order of presentation in the SELECT clause.
• We can sort on a combination of several columns.
• You can use an ascending or descending order, variable to each column.
• By default, the ascending order is implicit.

Move to the next article: Writing conditions with WHERE