MySQL Calculated Columns: Expressions in SELECT and DUAL

A SELECT can show more than stored columns. It can calculate new ones.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT name,
       price,
       price * 0.18                AS gst,
       price + price * 0.18        AS price_with_gst,
       ROUND(price * 1.18, 0)      AS rounded_price
FROM products
WHERE category = 'stationery';

Output

OUTPUT

+--------------+--------+---------+----------------+---------------+
| name         | price  | gst     | price_with_gst | rounded_price |
+--------------+--------+---------+----------------+---------------+
| Notebook     |  80.00 | 14.4000 |        94.4000 |            94 |
| Gel Pen      |  15.50 |  2.7900 |        18.2900 |            18 |
| Marker Set   | 145.00 | 26.1000 |       171.1000 |           171 |
| Sticky Notes |  35.00 |  6.3000 |        41.3000 |            41 |
+--------------+--------+---------+----------------+---------------+
4 rows in set

Operators and functions work on each row. The calculation happens in the query. Nothing is stored in the table. Change the tax rate in the query and the next result changes.

Text and numbers together

query.sql

SQL

SELECT CONCAT(name, ' (', category, ')') AS label,
       CONCAT('Rs. ', FORMAT(price, 2)) AS shown_price
FROM products
ORDER BY price DESC
LIMIT 3;

Output

OUTPUT

+----------------------+-------------+
| label                | shown_price |
+----------------------+-------------+
| Backpack (bags)      | Rs. 999.00  |
| Desk Lamp (home)     | Rs. 650.00  |
| Laptop Sleeve (bags) | Rs. 450.00  |
+----------------------+-------------+
3 rows in set

CONCAT joins text. FORMAT(number, decimals) adds thousand separators and returns text, so use it for display only, never for calculations.

Stock status with a calculation

query.sql

SQL

SELECT name, stock,
       stock = 0          AS out_of_stock,
       stock < 30         AS low_stock
FROM products
ORDER BY stock
LIMIT 4;

Output

OUTPUT

+---------------+-------+--------------+-----------+
| name          | stock | out_of_stock | low_stock |
+---------------+-------+--------------+-----------+
| Marker Set    |     0 |            1 |         1 |
| Laptop Sleeve |    18 |            0 |         1 |
| Backpack      |    25 |            0 |         1 |
| Desk Lamp     |    40 |            0 |         0 |
+---------------+-------+--------------+-----------+
4 rows in set

A comparison is an expression too. It returns 1 (true) or 0 (false). For labels, see CASE.

SELECT without FROM

FROM is optional when you only need to calculate:

query.sql

SQL

SELECT 2 * 21 AS answer, NOW() IS NOT NULL AS has_time, VERSION() IS NOT NULL AS has_version, UPPER('mysql') AS shouting;

Output

OUTPUT

+--------+----------+-------------+----------+
| answer | has_time | has_version | shouting |
+--------+----------+-------------+----------+
|     42 |        1 |           1 | MYSQL    |
+--------+----------+-------------+----------+
1 row in set

This is the best way to test a function or an expression. No table is needed.

DUAL

Some databases, such as Oracle, require a FROM, and use a dummy table called DUAL. MySQL accepts it for compatibility, but you never need it:

query.sql

SQL

SELECT 1 + 1 AS two FROM DUAL;

Output

OUTPUT

+-----+
| two |
+-----+
|   2 |
+-----+
1 row in set

Watch out for NULL

A calculation with NULL gives NULL. If a column may be NULL, decide what the result should be (NULL):

query.sql

SQL

SELECT 5 + NULL AS plain, 5 + IFNULL(NULL, 0) AS with_ifnull, COALESCE(NULL, NULL, 7) AS first_non_null;

Output

OUTPUT

+-------+-------------+----------------+
| plain | with_ifnull | first_non_null |
+-------+-------------+----------------+
|  NULL |           5 |              7 |
+-------+-------------+----------------+
1 row in set

Division

/ in MySQL always returns a decimal. DIV returns an integer. Division by zero returns NULL in a SELECT (with a warning), and is an error in an INSERT or UPDATE in strict mode:

query.sql

SQL

SELECT 10 / 4 AS decimal_division, 10 DIV 4 AS whole_division, 10 / 0 AS by_zero;

Output

OUTPUT

+------------------+----------------+---------+
| decimal_division | whole_division | by_zero |
+------------------+----------------+---------+
|           2.5000 |              2 |    NULL |
+------------------+----------------+---------+
1 row in set

Where to calculate

A calculation in the query is the right choice for display values. For something you need again and again, or must index, use a generated column (Generated Columns). For something that must be a stored historical fact (the price at the time of the order), store it. Prices change, and an old order must keep the old price.

lightbulb

Test expressions without a table

SELECT <expression>; is the quickest experiment. Try SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH); and read the result before you put it into a larger query.