SQLite SELECT

The SELECT statement retrieves data from one or more tables. It is the most frequently used SQL command. Every dashboard, report, search result, and data export you build starts with a SELECT statement.

Sample Table for This Topic

products
───────────────────────────────────────────────────
id │ name          │ category     │ price │ in_stock
───┼───────────────┼──────────────┼───────┼──────────
1  │ Notebook      │ Stationery   │ 2.99  │ 1
2  │ Pen           │ Stationery   │ 0.99  │ 1
3  │ Ruler         │ Stationery   │ 1.49  │ 1
4  │ Eraser        │ Stationery   │ 0.59  │ 1
5  │ Backpack      │ Bags         │ 24.99 │ 1
6  │ Water Bottle  │ Accessories  │ 8.50  │ 0

Select All Columns

SELECT * FROM products;

-- The asterisk * means "all columns"
-- Returns every column of every row

Select Specific Columns

SELECT name, price FROM products;

name          │ price
──────────────┼───────
Notebook      │ 2.99
Pen           │ 0.99
Ruler         │ 1.49
Eraser        │ 0.59
Backpack      │ 24.99
Water Bottle  │ 8.50

Column Aliases with AS

An alias gives a column a friendlier name in the output without changing the actual table.

SELECT name AS product_name,
       price AS cost
FROM products;

product_name  │ cost
──────────────┼───────
Notebook      │ 2.99
Pen           │ 0.99
...

Computed Columns

You can calculate new values directly inside a SELECT statement.

-- Price with 10% tax added
SELECT name,
       price,
       price * 1.10 AS price_with_tax
FROM products;

name      │ price │ price_with_tax
──────────┼───────┼───────────────
Notebook  │ 2.99  │ 3.289
Pen       │ 0.99  │ 1.089
Ruler     │ 1.49  │ 1.639
...

SELECT with Expressions

-- Convert in_stock integer to readable text
SELECT name,
       CASE in_stock
         WHEN 1 THEN 'Available'
         ELSE 'Out of Stock'
       END AS availability
FROM products;

name          │ availability
──────────────┼──────────────
Notebook      │ Available
Water Bottle  │ Out of Stock

SELECT Without a Table

SQLite allows SELECT without a FROM clause. Useful for quick calculations and function testing.

SELECT 2 + 2;          -- 4
SELECT upper('hello'); -- HELLO
SELECT date('now');    -- 2024-07-24
SELECT sqlite_version(); -- 3.46.0

SELECT Clause Order

SELECT   column_list          ← what to show
FROM     table_name           ← where to get data
WHERE    condition            ← which rows to include
GROUP BY column               ← how to group rows
HAVING   group_condition      ← filter after grouping
ORDER BY column               ← how to sort
LIMIT    number               ← how many rows max
OFFSET   number;              ← skip this many rows first

(Not all clauses are required — only SELECT is mandatory)

Selecting with Conditions (Preview)

-- Only products priced under $5
SELECT name, price
FROM products
WHERE price < 5.00;

name     │ price
─────────┼───────
Notebook │ 2.99
Pen      │ 0.99
Ruler    │ 1.49
Eraser   │ 0.59

Checking if a Column Is NULL

-- Find products with no category set
SELECT name FROM products WHERE category IS NULL;

-- Find products that DO have a category
SELECT name FROM products WHERE category IS NOT NULL;

Practical Example: Product Catalog Query

SELECT
  id,
  name                   AS product,
  category,
  printf('$%.2f', price) AS price,
  CASE in_stock WHEN 1 THEN 'Yes' ELSE 'No' END AS available
FROM products
ORDER BY category, price;

id │ product      │ category    │ price  │ available
───┼──────────────┼─────────────┼────────┼──────────
6  │ Water Bottle │ Accessories │ $8.50  │ No
5  │ Backpack     │ Bags        │ $24.99 │ Yes
4  │ Eraser       │ Stationery  │ $0.59  │ Yes
2  │ Pen          │ Stationery  │ $0.99  │ Yes
3  │ Ruler        │ Stationery  │ $1.49  │ Yes
1  │ Notebook     │ Stationery  │ $2.99  │ Yes

Key Takeaways

• SELECT * returns all columns; list specific column names to narrow output
• Use AS to rename a column in the result
• Expressions and functions work directly in the SELECT clause
• A CASE expression converts raw values into readable labels
• Full SELECT syntax: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT