SELECT | Engineering Interview Kit

Concept

The SELECT statement is the most frequently used command in SQL. It is used to fetch data from one or more tables.
A SELECT statement always creates a Result Set (a temporary table in memory) containing the requested data. It does not modify the underlying data on the hard drive.

Basic Syntax

1. Selecting Specific Columns

The best practice is to always explicitly list the columns you need.

SELECT first_name, email, salary 
FROM employees;

2. Selecting All Columns (The Star)

The asterisk (*) is a wildcard that means “return every single column”.

SELECT * FROM employees;

Creating Aliases (`AS`)

Sometimes database column names are ugly (e.g., usr_frst_nm). You can rename them in the output result set using the AS keyword. This is entirely temporary and does not rename the physical column in the database.

SELECT 
    usr_frst_nm AS first_name,
    usr_lst_nm AS last_name
FROM users;

Performing Math in SELECT

The SELECT clause isn’t just for fetching data; it acts like a calculator. You can perform arithmetic directly on the columns before returning them to Node.js.

-- Calculate the monthly salary on the fly
SELECT 
    name, 
    salary AS annual_salary, 
    (salary / 12) AS monthly_salary 
FROM employees;

Trade-Offs: The `SELECT *` Problem

In modern software engineering, writing SELECT * in production code is widely considered a terrible practice.

Pros: Fast to write during debugging or in a terminal.
Cons:
1. Network Bloat: If a table has 50 columns (including massive TEXT biographies or binary images), and your Node.js app only needs the email column, you are transmitting megabytes of useless data across the network, crushing your bandwidth.
2. Memory Exhaustion: Your Node.js server has to load all 50 columns into RAM to parse the JSON.
3. Brittle Code: If a DBA adds a new column to the table, SELECT * will suddenly return an extra field that your application code might not be expecting, potentially causing crashes.

Interview Questions

Q: In a massive table with 10 million rows, a developer runs SELECT COUNT(*) FROM users;. It takes 3 seconds to execute. How does PostgreSQL physically execute this, and why is it so slow compared to MySQL’s MyISAM engine?
A: Because of MVCC (Multi-Version Concurrency Control).
Older, simpler databases (like MyISAM) keep a single, globally updated metadata counter of total rows. When you ask for COUNT(*), it instantly returns that number in $O(1)$ time.
PostgreSQL uses MVCC. Because there might be 50 different concurrent transactions happening (some deleting rows, some inserting rows that haven’t been committed yet), there is no single “true” row count. To guarantee mathematical accuracy for the specific transaction running the query, PostgreSQL must perform a Sequential Scan (physically iterating through all 10 million rows on the disk) to verify if each individual row is currently visible to that specific query. This is extremely slow.