SELECT Queries

Introduction

SELECT reads data. It is the bread-and-butter of SQL — you'll write thousands of SELECTs in your career. Mastering it (filters, joins, aggregations, windows) is what separates beginners from senior engineers.

Beginner analogy: SELECT is asking the librarian: 'find me all books from 2020 by author X, sorted by title, give me the first 10.'

Understanding the topic

Core concepts to understand:

• SELECT col1, col2 FROM table WHERE ... ORDER BY ... LIMIT n;
• Use SELECT * only for exploration — name columns in production.
• Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
• DISTINCT removes duplicates.
• Combine with JOIN, GROUP BY, window functions for power.

Syntax reference

Visual workflow / architecture:

SELECT email, count(*) AS orders
FROM   orders o
JOIN   users  u ON u.id = o.user_id
WHERE  o.created_at > now() - interval '30 days'
GROUP  BY u.email
HAVING count(*) > 5
ORDER  BY orders DESC
LIMIT  10;

SELECT <columns>
FROM   <table>
[WHERE   <predicate>]
[GROUP BY <cols>]  [HAVING <agg-predicate>]
[ORDER BY <cols> [ASC|DESC]]
[LIMIT <n> [OFFSET <m>]];

SELECT email, plan
FROM   users
WHERE  plan = 'pro'
ORDER  BY created_at DESC
LIMIT  3;

Real-world use

Every dashboard, report, leaderboard, search page and analytics widget is a SELECT. Twitter's feed, Stripe's reports, Notion's search — all SELECTs over carefully designed tables and indexes.

Best practices

• Always specify columns — never SELECT * in production.
• Always have a WHERE on big tables to use indexes.
• Use EXPLAIN to verify the plan is good.

Common mistakes

• SELECT * over a 200-column table → wasteful I/O.
• Forgetting LIMIT on huge tables → app OOM.
• ORDER BY without an index on the order column → slow sorts.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Logical execution order of a SELECT.
• Q2. Why avoid SELECT * in production?
• Q3. Difference between WHERE and HAVING.
• Q4. What does DISTINCT do?
• Q5. How to limit rows in Postgres vs SQL Server?