SQL AS: rename a column and compute values

A query does more than copy the columns of a table: it can compute new values (a price including tax, a stock value) and join text together (a full name). The AS keyword then gives these computed columns a clear name.

Syntax

SELECT expression AS new_name
FROM table_name;

How it works

Calculations

In the SELECT list, you can use the +, -, * and / operators between columns and numbers. The calculation is done for each row.

⚠️ In SQLite, dividing two integers gives an integer: 7 / 2 equals 3. To get 3.5, write 7 / 2.0 or multiply by 1.0 first.

Joining text

In SQLite (as in PostgreSQL and Oracle), the || operator glues two pieces of text together. MySQL uses the CONCAT() function instead, which recent SQLite versions also accept.

Aliases with AS

Without an alias, a computed column is named after its full expression, for example prix * stock. With AS valeur_stock, the result becomes readable. The AS keyword is optional (prix * stock valeur_stock also works), but writing it avoids a lot of confusion. Aliases are also used to shorten table names in joins.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT nom, prix, stock, prix * stock AS valeur_stock
FROM produits
LIMIT 6;

SELECT nom, prix AS prix_ht, ROUND(prix * 1.2, 2) AS prix_ttc
FROM produits
LIMIT 6;

SELECT prenom || ' ' || nom AS nom_complet, poste
FROM employes
LIMIT 6;

Common mistakes

SELECT 7 / 2 AS resultat;

SELECT 7 / 2.0 AS resultat;

SELECT prenom + ' ' + nom FROM clients;

SELECT prenom || ' ' || nom FROM clients;

SELECT prix * 1.2 AS prix ttc FROM produits;

SELECT prix * 1.2 AS prix_ttc FROM produits;

Exercises

Try writing the query yourself before opening the solution.

SELECT nom, ROUND(prix * 1.1, 2) AS nouveau_prix FROM produits;

SELECT nom || ' (' || ville || ')' AS identite FROM clients;

Frequently asked questions