SQL SELECT — SELECT * Fails After Schema Migration

Every app you use — Spotify, Amazon, your bank — stores data in a database. When Spotify shows you your last-played songs, or Amazon lists your order history, something had to go fetch that data. That something is a SQL SELECT statement. It's the most-used command in all of SQL, and understanding it deeply is the foundation of working with any database system, whether you're a developer, data analyst, or product manager.

What a Database Table Actually Looks Like

Before you can SELECT anything, you need to understand what you're selecting from. A database stores data in tables — think of a table exactly like a spreadsheet. It has columns (the categories of data, like 'first_name' or 'price') and rows (the actual individual records, like one specific customer or one product).

For example, an e-commerce site might have a table called 'products'. Each row is one product. Each column holds one type of information about that product — its name, price, how many are in stock, and so on.

The SELECT statement lets you look at that table's data. You decide WHICH columns to show and, optionally, WHICH rows to include. Everything flows from that one simple idea.

-- This is what our 'products' table looks like before we query it.
-- Think of this as the raw spreadsheet sitting in the database.

-- TABLE: products
-- +------------+---------------------+-------+-------+
-- | product_id | product_name        | price | stock |
-- +------------+---------------------+-------+-------+
-- |          1 | Wireless Headphones | 79.99 |   120 |
-- |          2 | USB-C Charging Cable|  9.99 |   500 |
-- |          3 | Laptop Stand        | 34.99 |    85 |
-- |          4 | Mechanical Keyboard |119.99 |    40 |
-- |          5 | Webcam HD 1080p     | 54.99 |   200 |
-- +------------+---------------------+-------+-------+

-- We'll use this table in every example below.
-- You can create it yourself with:
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(6, 2),
    stock        INT
);

INSERT INTO products VALUES (1, 'Wireless Headphones',  79.99, 120);
INSERT INTO products VALUES (2, 'USB-C Charging Cable',  9.99, 500);
INSERT INTO products VALUES (3, 'Laptop Stand',         34.99,  85);
INSERT INTO products VALUES (4, 'Mechanical Keyboard', 119.99,  40);
INSERT INTO products VALUES (5, 'Webcam HD 1080p',      54.99, 200);

Your First SELECT — Fetching All Rows and Columns

The simplest SELECT statement says: 'Give me everything from this table.' The syntax is two words followed by two more:

SELECT * FROM table_name;

The asterisk (*) is shorthand for 'all columns'. FROM tells SQL which table to look in. The semicolon ends the statement — think of it as a period at the end of a sentence.

This is the 'blast radius' query. It's great for exploring a table you've never seen before. You'll use it constantly when you first connect to a new database just to see what's there.

That said, SELECT * is a starting point, not a habit. In real applications you almost always want specific columns — we'll get there in the next section.

-- SELECT: the command that retrieves data
-- *: a wildcard meaning 'every column in this table'
-- FROM: tells SQL which table to look in
-- products: our table name

SELECT * FROM products;

Selecting Specific Columns — Only Get What You Need

Instead of the wildcard *, you can list exactly which columns you want, separated by commas. This is called a column list, and it's how 99% of real-world queries are written.

Why does this matter? Imagine your products table has 30 columns — including internal cost prices, supplier IDs, and audit timestamps. If you're building a page that just shows customers the product name and price, why fetch all 30 columns? You'd be doing extra work for nothing.

Naming your columns also makes your query self-documenting. Anyone reading SELECT product_name, price FROM products immediately knows what this query is for. SELECT * tells them nothing.

You can list columns in any order — SQL returns them in the order you specify, not the order they're stored in the table.

-- Fetch only the product name and price — ideal for a public-facing product listing page.
-- Notice: we list column names separated by commas.
-- The columns appear in the OUTPUT in the order we list them here.

SELECT product_name, price
FROM products;

-- -------------------------------------------------
-- You can also flip the column order — SQL doesn't care.
-- This returns price FIRST, then product_name.

SELECT price, product_name
FROM products;

Column Aliases — Giving Your Results Friendlier Names

Sometimes a column name in the database is technical or unclear. 'product_name' is fine, but what if a column were called 'prd_nm_v2' or something equally cryptic? Aliases let you rename columns in your output without touching the actual database.

You create an alias using the AS keyword after the column name. The alias only exists for the duration of that query — the table in the database is completely unchanged.

Aliases are also essential when you use calculated columns. If you multiply price by 1.2 to add tax, the result column has no name by default — SQL might call it something ugly like '(price * 1.2)'. An alias gives it a clean, readable name like 'price_with_tax'.

This is hugely useful when your app reads column names from query results, because your code can rely on the alias staying consistent even if the underlying column name changes.

-- AS gives a column a temporary friendly name in the output.
-- 'product_name AS name' means: show this column but call it 'name'
-- 'price AS price_usd' — clearer for international apps

SELECT
    product_name  AS name,
    price         AS price_usd,
    stock         AS units_available
FROM products;

-- -------------------------------------------------
-- Calculated column with alias:
-- We add 20% tax to the price and label the result cleanly.
-- Without the alias, the column header would be '(price * 1.20)'

SELECT
    product_name         AS name,
    price                AS base_price,
    price * 1.20         AS price_with_tax   -- calculated column with alias
FROM products;

Filtering Rows with WHERE — The Real Power of SELECT

So far every query returned all 5 rows. In real life, tables have thousands or millions of rows, and you rarely want all of them. The WHERE clause lets you set conditions so SQL only returns rows that match.

Think of WHERE like a security guard at the exit of a warehouse. Every row tries to leave. The guard checks each one against your condition. Only rows that pass the check make it into your results.

You can filter by equality (=), comparisons (>, <, >=, <=), or inequality (<> or !=). You can combine multiple conditions using AND (both must be true) or OR (either one can be true).

The WHERE clause is evaluated before the columns are selected — SQL finds the matching rows first, then picks the columns you asked for. Understanding this order matters when you start writing more complex queries.

-- Example 1: Find all products that cost more than $50
-- The WHERE clause checks each row's 'price' column against 50.
-- Only rows where price > 50 are returned.

SELECT product_name, price
FROM products
WHERE price > 50;

-- -------------------------------------------------
-- Example 2: Find products that are BOTH affordable AND well-stocked
-- AND means BOTH conditions must be true for a row to appear.

SELECT product_name, price, stock
FROM products
WHERE price < 50
  AND stock > 100;

-- -------------------------------------------------
-- Example 3: Find a specific product by its exact name
-- String values go inside single quotes — this is required in SQL.

SELECT product_id, product_name, price
FROM products
WHERE product_name = 'Laptop Stand';