SELECT columns and aliases
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A query is a question you ask the database, and SELECT is the statement that asks it. You name the columns you want and the table they come from, and MySQL answers with a result table.
SELECT * and naming columns
A SELECT statement reads rows from a table and returns them as a result table. It only reads data and never changes the table. * means every column; a list of names means only those columns.
An app screen needs data out of a table. The first query below reads every column of categories; the second names just two columns of customers, in the order the result should show them.
Example 1 · Every column with SELECT *
SELECT * FROM categoriesORDER BY category_id;+-------------+-------------+-----------+ | category_id | name | parent_id | +-------------+-------------+-----------+ | 1 | Electronics | NULL | | 2 | Mobiles | 1 | | 3 | Laptops | 1 | | 4 | Accessories | 1 | | 5 | Books | NULL | | 6 | Fashion | NULL | | 7 | Toys | NULL | +-------------+-------------+-----------+
What this code does
*returns every column ofcategoriesin the order the table defines them:category_id,name,parent_id.ORDER BY category_idmakes the rows come back in id order every time. ANULLinparent_idmeans the category has no parent; the NULL lesson explains it.
Example 2 · Only the columns you name
SELECT city, nameFROM customersORDER BY customer_id;+-----------+--------------+ | city | name | +-----------+--------------+ | Bengaluru | Asha Rao | | Chennai | Ravi Kumar | | Chennai | Meena Iyer | | Delhi | Kabir Singh | | Pune | Priya Sharma | | Mumbai | Arjun Mehta | | Kochi | Divya Nair | | Delhi | Rahul Verma | | Pune | Sneha Patil | | Hyderabad | Imran Khan | +-----------+--------------+
What this code does
- The result has two columns, in the order written after
SELECT:cityfirst, thenname. The table still has six columns; the rest are just not returned. - Splitting a query over several lines is only for reading. MySQL runs it when it reaches the
;.
Without SELECT you could store data but never get it back: every screen of an app, from a product list to an exam result page, runs a SELECT behind it.
ORDER BY customer_idat the end gives the rows a fixed order. Without it, MySQL may return rows in any order. The ORDER BY lesson covers sorting fully.- Keywords like
SELECTandFROMare not case-sensitive. Writing them in capitals is a habit that makes queries easy to read.
Expressions in SELECT
An expression is a calculation that MySQL works out for every row, such as price * stock. It can sit in the SELECT list next to plain columns, and it exists only in the result, never in the table.
The table stores the parts of a fare, but the screen needs the total. Put the calculation in SELECT and MySQL does it once for every row, so the application receives the answer ready.
Example 3 · Calculated columns: total fare per train
DROP TABLE IF EXISTS train_fares;CREATE TABLE train_fares ( train_no INT PRIMARY KEY, train_name VARCHAR(40) NOT NULL, base_fare DECIMAL(8,2) NOT NULL, reservation_charge DECIMAL(6,2) NOT NULL, distance_km INT NOT NULL);INSERT INTO train_fares VALUES (12007, 'Mysuru Shatabdi', 755.00, 40.00, 500), (12627, 'Karnataka Express', 615.00, 20.00, 2367), (22691, 'Rajdhani Express', 2940.00, 50.00, 2365);
SELECT train_name, base_fare + reservation_charge, base_fare / distance_kmFROM train_faresORDER BY train_no;+-------------------+--------------------------------+-------------------------+ | train_name | base_fare + reservation_charge | base_fare / distance_km | +-------------------+--------------------------------+-------------------------+ | Mysuru Shatabdi | 795.00 | 1.510000 | | Karnataka Express | 635.00 | 0.259823 | | Rajdhani Express | 2990.00 | 1.243129 | +-------------------+--------------------------------+-------------------------+
What this code does
- Both calculations run once per row. The table stores only the parts; the total fare and the fare per km exist only in this result.
/always returns a decimal. MySQL adds four decimal places to those of the number being divided, so755.00 / 500shows six:1.510000.- The headings are the expression text, which is hard to read and hard for an application to use.
The usual operators work: +, -, * and /. Division with / always gives a decimal answer, so 7 / 2 is 3.5000, not 3. You can even run SELECT 12 * 3; with no table at all, to use MySQL as a calculator.
price * stock. The next section fixes that.Column aliases with AS
A column alias is a new name for a result column, given with AS, as in price * stock AS stock_value. It changes only the heading in the result, never the table.
A heading such as price * stock is hard to read, and an application finds each value by its column name. Give the column a short alias with AS, then see how a missing comma behaves.
Example 4 · Readable names with AS
SELECT name AS product, price, stock, price * stock AS stock_value, price * 0.90 AS sale_priceFROM productsORDER BY product_id;+------------------------+----------+-------+-------------+------------+ | product | price | stock | stock_value | sale_price | +------------------------+----------+-------+-------------+------------+ | Redmi Note 13 | 17999.00 | 40 | 719960.00 | 16199.1000 | | Samsung Galaxy M35 | 19999.00 | 25 | 499975.00 | 17999.1000 | | iPhone 15 | 69900.00 | 8 | 559200.00 | 62910.0000 | | Lenovo IdeaPad Slim 3 | 42990.00 | 12 | 515880.00 | 38691.0000 | | HP Victus Gaming | 64990.00 | 5 | 324950.00 | 58491.0000 | | boAt Airdopes 141 | 1299.00 | 120 | 155880.00 | 1169.1000 | | Mi Power Bank 20000mAh | 2199.00 | 60 | 131940.00 | 1979.1000 | | Logitech M331 Mouse | 849.00 | 0 | 0.00 | 764.1000 | | Clean Code | 499.00 | 30 | 14970.00 | 449.1000 | | Hands-On Python | 650.00 | 22 | 14300.00 | 585.0000 | | Atomic Habits | 399.00 | 50 | 19950.00 | 359.1000 | | Cotton Kurta | 899.00 | 35 | 31465.00 | 809.1000 | | Running Shoes | 2499.00 | 18 | 44982.00 | 2249.1000 | | Rubik's Cube | 299.00 | 70 | 20930.00 | 269.1000 | | Gift Card Rs. 500 | 500.00 | 999 | 499500.00 | 450.0000 | +------------------------+----------+-------+-------------+------------+
What this code does
AS stock_valuenames the calculated column, andname AS productrenames a plain column. Theproductstable keeps its own column names.price * 0.90gives a festival sale price with 10% off. It shows four decimal places becauseDECIMAL(10,2)times0.90keeps every digit; the Number and date functions lesson shows how to round it.
Example 5 · Two mistakes: a missing comma and a misspelt column
-- Meant: name, price. The comma is missing.SELECT name priceFROM productsWHERE product_id <= 3ORDER BY product_id;
-- The column is called price, not prise.SELECT name, priseFROM products;+--------------------+ | price | +--------------------+ | Redmi Note 13 | | Samsung Galaxy M35 | | iPhone 15 | +--------------------+ ERROR 1054 (42S22): Unknown column 'prise' in 'field list'
What this code does
- The first query runs without any error, but it returns the product names under the heading
price: MySQL readpriceas an alias forname. (WHERE product_id <= 3keeps the first three rows; the next lesson coversWHERE.) - The second query fails with error 1054,
Unknown column 'prise' in 'field list'. The field list is the list of columns afterSELECT, so the message tells you exactly where to look.
AS is optional: price * stock stock_value works too. That is exactly why a missing comma is dangerous. SELECT name price FROM products does not fail; it returns one column, the product names, headed price.
- Use simple aliases in snake_case, such as
sale_price. They need no quotes. - An alias with a space needs quotes:
AS 'Stock value'. It works, but such names are awkward to use later, so avoid them. - Always write
AS. It makes the missing-comma mistake easy to spot.
Predict the output
What does this query return?
Read the code and pick what it prints.
Round 1 of 2
A comma is missing. What comes back?
Why SELECT * is avoided in applications
SELECT * returns every column of a table, in the order the table defines them. It is fine for exploring a table, but application code lists the columns it needs by name.
SELECT * is perfect for looking at a table. But if someone adds a column later, it quietly returns a different shape. The example adds rent_per_month to a table, then runs both styles.
Example 6 · SELECT * changes shape when the table changes
DROP TABLE IF EXISTS hostel_rooms;CREATE TABLE hostel_rooms ( room_no INT PRIMARY KEY, block CHAR(1) NOT NULL, beds INT NOT NULL);INSERT INTO hostel_rooms VALUES (101, 'A', 2), (102, 'A', 3), (201, 'B', 2);
-- Later, the warden asks for a monthly rent column.ALTER TABLE hostel_rooms ADD COLUMN rent_per_month INT NOT NULL DEFAULT 4500;
SELECT * FROM hostel_rooms ORDER BY room_no;SELECT room_no, block, beds FROM hostel_rooms ORDER BY room_no;+---------+-------+------+----------------+ | room_no | block | beds | rent_per_month | +---------+-------+------+----------------+ | 101 | A | 2 | 4500 | | 102 | A | 3 | 4500 | | 201 | B | 2 | 4500 | +---------+-------+------+----------------+ +---------+-------+------+ | room_no | block | beds | +---------+-------+------+ | 101 | A | 2 | | 102 | A | 3 | | 201 | B | 2 | +---------+-------+------+
What this code does
- After
ALTER TABLE,SELECT *returns four columns instead of three. Code that expected three columns, or read them by position, can now break. - The query with named columns returns exactly the same three columns as before the change. This is why application code names its columns.
Naming columns also sends only what the screen needs, and a private column such as a password hash is never read by accident. A customer list needs a name and a city, not every phone number.
- Exploring a table in the mysql client:
SELECT *is fine. - A table with large text or image columns:
SELECT *reads all of them, every time.
Next step
Check your understanding
11 quick questions