SQLite SELECT: Querying Rows, Columns, and Expressions

SELECT Reads Rows From a Table

Every question you ask a SQLite database starts with SELECT. It says: give me these columns, from this table, for the rows that match these conditions. Today we're focused on the first two pieces — columns and table. Filtering comes in the next doc.

The simplest possible query:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products (name, price, stock) VALUES
    ('Notebook', 4.50, 120),
    ('Pen', 1.20, 500),
    ('Backpack', 39.99, 25),
    ('Eraser', 0.80, 300);

SELECT * FROM products;

SELECT * means "every column." FROM products names the table. The result is one row per record, with all four columns. The semicolon ends the statement.

That's the whole shape: SELECT <columns> FROM <table>;.

Picking Specific Columns

* is convenient when you're exploring. In real code, you almost always want to name the columns you actually care about:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products (name, price, stock) VALUES
    ('Notebook', 4.50, 120),
    ('Pen', 1.20, 500),
    ('Backpack', 39.99, 25);

SELECT name, price FROM products;

You get back two columns, in the order you asked for them. The order in the SELECT list controls the order in the result — it doesn't have to match the table's column order.

Why prefer this over SELECT *? Two reasons. First, less data crosses the wire (or comes out of the file), which matters when tables get wide. Second, your query stays stable when someone adds a column to the table next month. SELECT * quietly starts returning more, which can break application code that unpacks rows by position.

SELECT Can Compute, Not Just Fetch

The columns in a SELECT list don't have to be raw column names. They can be any expression — arithmetic, function calls, string concatenation, constants. SQLite evaluates the expression for each row.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products (name, price, stock) VALUES
    ('Notebook', 4.50, 120),
    ('Pen', 1.20, 500),
    ('Backpack', 39.99, 25);

SELECT name, price, stock, price * stock FROM products;

The fourth column doesn't exist in the table — SQLite computed it row by row. You can do the same with string functions, dates, conditional expressions, anything. A SELECT list is a place to shape data, not just retrieve it.

You can even SELECT without a FROM clause, treating SQLite as a calculator:

SELECT 2 + 2, upper('hello'), length('SQLite');

One row, three columns. Useful for testing what an expression does before plugging it into a real query.

Aliasing Columns With AS

When you compute a column, SQLite picks a default name for it — often the expression itself, like price * stock. That's ugly and unstable. Give the column a real name with AS:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products (name, price, stock) VALUES
    ('Notebook', 4.50, 120),
    ('Backpack', 39.99, 25);

SELECT
    name AS product,
    price * stock AS inventory_value
FROM products;

The alias is the column's name in the result set. That matters for readability when you're reading output, and it matters a lot for application code that pulls values by column name (row["inventory_value"] rather than row[1]).

The AS keyword is optional — price * stock inventory_value works the same way — but spell it out. The intent is clearer, and it avoids confusing readers who might mistake a missing comma for an alias.

Aliasing the Table Too

You can also give the table itself a short alias. It's not very useful with one table, but the habit pays off the moment you start joining:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

INSERT INTO products (name, price) VALUES ('Notebook', 4.50);

SELECT p.name, p.price FROM products AS p;

p is now a stand-in for products. The dotted form p.name is explicit about which table a column comes from. With one table that's just noise; with three joined tables it's the only thing keeping the query readable.

Literals and Constants in the Result

You can mix actual columns with constant values. Useful when you want to tag rows or fill in a placeholder:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Pen', 1.20);

SELECT
    name,
    price,
    'USD' AS currency,
    1 AS active
FROM products;

Every row gets the same 'USD' and 1. This pattern shows up when you're combining results from multiple queries with UNION and need a marker column to tell the sources apart.

A Word on NULL

Some columns are empty — no value was inserted, or the column has no default. SQLite represents "no value" as NULL, and NULL shows up in SELECT results as a blank or the literal word NULL depending on the tool you're using.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Mystery Item', NULL);

SELECT name, price, price * 2 AS doubled FROM products;

Look at the second row. price is NULL, and price * 2 is also NULL — any arithmetic involving NULL produces NULL. This is one of the most common sources of confused SQL results, and it has its own page coming up. For now, just notice that NULL is contagious through expressions.

What You Take Away

• SELECT <columns> FROM <table>; is the shape of every query.
• List columns explicitly in real code; save SELECT * for exploring.
• The SELECT list can hold any expression, not just column names.
• Use AS to give computed columns and aliased tables proper names.
• NULL propagates through arithmetic — keep that in mind when results look off.

Next: Filtering With WHERE

So far every query returns every row in the table. The WHERE clause is how you narrow that down — pick rows by price, by name, by date, by any expression that evaluates to true or false. That's the next doc.