SELECT

Hook: Interviewers love SELECT because it looks simple, but it quietly tests whether you understand how SQL chooses rows, shapes columns, and filters data.

Question: What does SELECT do in SQL?

Answer: SELECT is the part of SQL that asks the database to return data. You use it to choose columns, compute new values, remove duplicates, sort results, and work with filtering clauses like WHERE. It does not change the table; it only returns a result set.

Interview-Ready Answer: I use SELECT when I want to read data from one or more tables. It lets me pick specific columns, compute expressions, and combine it with WHERE to filter rows before they are returned. One important detail is that SQL is logically processed in a different order than it is written, so WHERE happens before the final SELECT output, which is why aliases usually are not visible there. SELECT itself is read-only: it returns rows, but it does not modify the underlying data.

What SELECT really means

Detailed Explanation: Think of SELECT as the shape part of a query: after SQL finds the relevant rows, SELECT decides which columns and expressions appear in the output. It can return raw columns like name, computed values like salary * 12, or even labels with aliases like salary * 12 AS annual_salary.

How it works under the hood

1. Read the source rows. The database starts from the table or joined tables in the FROM clause.
2. Filter rows early. WHERE removes rows that do not match the condition. This is row-by-row filtering.
3. Build the output columns. SELECT evaluates expressions for the rows that survived filtering.
4. Remove duplicates if asked. SELECT DISTINCT collapses duplicate result rows.
5. Sort and trim. ORDER BY sorts the final rows, and LIMIT/FETCH returns only the first few.

This logical order matters for interview questions: even though you write SELECT ... FROM ... WHERE ... ORDER BY ..., the database does not literally execute it left to right. The optimizer, which is the database’s query planner, may rewrite the physical work to be faster, but the logical meaning stays the same.

WHERE, HAVING, and ORDER BY

Memory rule: WHERE removes bad rows, SELECT chooses what to show, and ORDER BY arranges the tray before it is served.

Performance and edge cases

There is no single time complexity for SELECT; cost depends on what the query does. A full table scan is roughly O(n), an index lookup can be closer to O(log n + k) where k is the number of matched rows, and sorting is usually O(n log n). That is why SELECT * on a huge table can be slow: the database may read more columns than you need and move more data around.

• NULL handling: = NULL never matches; use IS NULL.
• Aliases: a column alias from SELECT is usually visible in ORDER BY, but not in WHERE.
• Distinct: DISTINCT may require sorting or hashing, so it can be expensive on large results.
• Version note: many databases support LIMIT, while standard SQL uses FETCH FIRST n ROWS ONLY.

Real-World Example: Imagine a checkout service showing the last 20 paid orders on an admin dashboard. The query uses SELECT to return only order id, customer email, total, and created time, plus a WHERE status = 'PAID' filter so cancelled or pending orders do not appear. If someone writes SELECT * and forgets the filter, the dashboard can become slow, return sensitive fields, and flood logs with a much larger payload.

What goes wrong: users complain that the page takes 8-10 seconds to load, the database shows a full scan in the slow query log, and support agents notice cancelled orders showing up in the UI. The fix is usually to narrow the SELECT list, add the right WHERE conditions, and make sure indexes support the filter columns.

Follow-up & Tricky Questions:

• What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping, while HAVING filters grouped results after aggregates like COUNT or SUM are computed.
• Can I use a SELECT alias inside WHERE? Usually no, because WHERE is logically evaluated before the SELECT list is produced. You can often reuse that alias in ORDER BY, though.
• Why is SELECT * considered risky? It can read more data than needed, make queries slower, and break client code when new columns are added or column order changes.
• What does DISTINCT do? It removes duplicate rows from the final result set. That can be useful, but on large data it may require extra work such as sorting or hashing.
• How do I return only the first few rows? Use LIMIT in many databases or FETCH FIRST n ROWS ONLY in standard SQL. Pair it with ORDER BY so the result is deterministic.
• Does SELECT modify data? No. It is read-only; to change data you need INSERT, UPDATE, or DELETE.
• Tricky: does = NULL work? No. Use IS NULL or IS NOT NULL, because NULL means unknown, not a normal value.
• Tricky: is ORDER BY applied before WHERE? No. Logically, filtering happens first, then ordering happens on the surviving rows.
• Tricky: does COUNT(col) count nulls? No. COUNT(col) ignores nulls; COUNT(*) counts rows.

Common Mistakes:

• Using SELECT * everywhere. Correction: choose only the columns you need so queries stay clearer and lighter.
• Putting row filters in HAVING when WHERE is better. Correction: use WHERE for normal row filtering and reserve HAVING for aggregate filters.
• Comparing to NULL with =. Correction: use IS NULL and IS NOT NULL.
• Assuming SQL runs in written order. Correction: remember the logical flow: FROM -> WHERE -> SELECT -> ORDER BY.

Memory Hook: Think of SQL like a cafeteria tray: WHERE removes the items you do not want, SELECT chooses what goes on the tray, and ORDER BY arranges it before it reaches you.

Cheat Sheet:

• SELECT returns data; it does not change data.
• WHERE filters rows before the final output.
• SELECT can return columns, expressions, aliases, and duplicates can be removed with DISTINCT.
• ORDER BY sorts the result set; LIMIT/FETCH trims it.
• NULL needs IS NULL, not = NULL.

Practice Tasks:

• Write a SELECT query that returns only employees with salary above a threshold.
• Rewrite SELECT * as a query that returns only three useful columns.
• Try one query that uses IS NULL and another that uses DISTINCT.