In the SQL Introduction you ran SELECT * FROM Categories;. That is the simplest form of the SQL SELECT statement. In this lesson you will make the result look exactly the way you need: only the right columns, with friendly names, extra calculated values and no duplicates.
The basic shape of a SQL SELECT query
Every SELECT query starts with a list of things to return, followed by FROM and the table to read from:
SELECT ProductName, Unit, Price
FROM Products;Returns all 15 products, showing name, pack size and price.
The result of a query is itself a table – a temporary one that exists only on your screen. The original Products table is not changed in any way. A SQL SELECT only reads.
Why not always use SELECT *?
SELECT * is handy for a quick look, but in real code you should name the columns. There are three good reasons:
- Speed: the database sends less data when you ask for fewer columns. On large tables with long text columns, this matters.
- Safety: if someone adds a column later (say, a password hash),
SELECT *quietly starts returning it too. - Clarity: anyone reading the query can see exactly which data it uses.
Renaming columns with AS
An alias gives a column a different name in the result. Write AS after the column, then the new name. This does not rename anything in the table; it only changes the heading of the result.
If the alias contains spaces, wrap it in double quotes. MySQL also accepts backticks (`Product Name`) and SQL Server accepts square brackets ([Product Name]), but double quotes are the standard SQL way. Most developers simply avoid spaces and use names like product_name.
Calculated columns
A column in the result does not have to exist in the table. You can do arithmetic with +, -, * and /, and use functions such as ROUND() and UPPER(). The example below adds 5% GST to each price and rounds the answer to two decimal places.
SELECT ProductName AS "Product Name",
Price AS BasePrice,
ROUND(Price * 1.05, 2) AS PriceWithGST
FROM Products;Returns 15 rows. For Filter Coffee Powder, BasePrice is 280 and PriceWithGST is 294.
Watch out: Watch out for integer division. In SQLite (and SQL Server), 7 / 2 gives 3 because both numbers are whole numbers. Write 7 / 2.0 or 7 * 1.0 / 2 to get 3.5. MySQL is different: its / always returns a decimal result.
Joining text together
To join two text values, SQLite and PostgreSQL use the || operator. MySQL uses the CONCAT() function instead, because in MySQL || means logical OR by default. SQLite 3.44 and later also understand CONCAT(), so it is worth knowing both.
SELECT FirstName || ' ' || LastName AS FullName,
Department,
Salary / 12 AS MonthlySalary
FROM Employees;Returns 8 rows, such as Karthik Raman, Management, 12500.
Notice that Salary / 12 gives whole numbers here, because Salary is stored as an integer – the warning above in action. Try changing it to Salary / 12.0 and run it again.
Removing duplicates with DISTINCT
Sometimes you want to know which different values exist in a column, not every row. SELECT DISTINCT removes duplicate rows from the result. For example, there are eight employees but only five departments:
SELECT DISTINCT Department
FROM Employees;Returns 5 rows: Management, Sales, Design, Development, Support.
A common use of DISTINCT is filling a drop-down list on a website – for example, a list of cities to filter customers by. You want each city once, not once per customer. Keep in mind that DISTINCT makes the database compare every row of the result, so only use it when you really expect duplicates.
DISTINCT looks at the whole row, not just the first column. SELECT DISTINCT State, Country FROM Customers; returns each different combination of state and country. It also treats empty values (NULL) as equal to each other, so all the missing states collapse into one row.
SELECT without a table
In SQLite and MySQL you can leave out FROM to test an expression quickly, for example SELECT 18 * 12; or SELECT UPPER('chennai');. This is a nice way to try out functions before you use them on real data. (Oracle needs FROM dual for the same thing.)
Order of the result
Without instructions, the database may return rows in any order it likes. In our small tables it usually matches the order the rows were inserted, but you must not rely on that. When order matters, use ORDER BY, covered in SQL ORDER BY and LIMIT. Next, though, you will learn to filter rows in SQL WHERE.