Sometimes an SQL query field is calculated based on other table columns. Imagine a table with
┌────────┬──────────┐ │ income │ tax_rate │ ├────────┼──────────┤ │ 70 │ 0.22 │ │ 84 │ 0.22 │ │ 90 │ 0.24 │ └────────┴──────────┘
You can calculate the annual tax:
select id, income * tax_rate as tax from people;
In order not to repeat these calculations everywhere, it is convenient to create a virtual generated column:
alter table people add column tax real as ( income * tax_rate );
After that, the column can be used in queries in the same way as regular columns:
select id, tax from people;
Virtual columns are not stored in the database and are calculated on the fly. But you can build an index on them to speed up data retrieval.
Strictly speaking, SQLite has virtual generated columns and stored ones. The latter are stored on disk, but it is impossible to create them via
alter table, so people mostly use virtual ones.
In general, the syntax is as follows:
alter table TABLE add column COLUMN TYPE as (EXPRESSION);
Generated column expression can include any table columns, but not other tables or subquery results. It’s for the best: for more complex combinations, there are views and temp tables. Let’s talk about them some other time.