← Back to Curriculum

The SELECT Statement

📚 Lesson 2 of 15 ⏱️ 30 min

The SELECT Statement

30 min

The `SELECT` statement is used to query the database and retrieve data that matches criteria that you specify. SELECT is the most fundamental and frequently used SQL command. It allows you to retrieve data from one or more tables, transform it, and format the results. Understanding SELECT is the foundation of all database querying. The basic syntax is `SELECT columns FROM table`, but SELECT can be much more powerful with additional clauses.

`SELECT *` selects all columns from a table. The asterisk (*) is a wildcard that represents all columns. While convenient for quick queries, `SELECT *` is generally discouraged in production code because it's less efficient, less readable, and breaks if table structure changes. Explicitly listing columns makes queries clearer and more maintainable. Use `SELECT *` for exploration, but specify columns in production code.

You can select specific columns by listing their names: `SELECT column1, column2 FROM table_name;`. Selecting specific columns improves performance (less data transferred), makes queries self-documenting, and prevents issues if table structure changes. Column order in SELECT determines result order. You can select columns from multiple tables when using JOINs. Understanding column selection is fundamental to writing efficient queries.

The `DISTINCT` keyword can be used to return only unique values. `SELECT DISTINCT column` eliminates duplicate rows from results. DISTINCT can be applied to single columns or multiple columns (returning unique combinations). DISTINCT operations can be expensive on large tables, so use them judiciously. Understanding when to use DISTINCT helps you get accurate results without unnecessary performance overhead.

Column aliases can be used to rename columns in the result set. Aliases make results more readable and are essential when using functions or expressions. Syntax: `SELECT column AS alias` or `SELECT column alias`. Aliases are required when using aggregate functions or when the same column name appears in multiple tables. Understanding aliases helps you create user-friendly query results.

SELECT can include expressions, functions, and calculations. You can perform arithmetic operations (`price * quantity`), use functions (`UPPER(name)`, `COUNT(*)`), and combine columns (`CONCAT(first_name, ' ', last_name)`). These capabilities make SELECT powerful for data transformation. Understanding SQL functions and expressions enables you to write sophisticated queries that process data as it's retrieved.

Key Concepts

  • SELECT retrieves data from database tables.
  • SELECT * selects all columns (use sparingly in production).
  • Specify columns explicitly for better performance and clarity.
  • DISTINCT eliminates duplicate rows from results.
  • Column aliases rename columns in query results.

Learning Objectives

Master

  • Writing basic SELECT queries
  • Selecting specific columns vs SELECT *
  • Using DISTINCT to eliminate duplicates
  • Creating column aliases for readable results

Develop

  • Understanding query performance implications
  • Writing clear, maintainable SQL queries
  • Understanding data retrieval patterns

Tips

  • Always specify column names instead of using SELECT * in production.
  • Use DISTINCT only when necessary—it can be slow on large tables.
  • Use meaningful aliases for calculated columns or functions.
  • Test queries on small datasets before running on large tables.

Common Pitfalls

  • Using SELECT * in production, causing performance issues and fragility.
  • Not understanding that DISTINCT works on entire rows, not individual columns.
  • Forgetting that column order matters in SELECT (determines result order).
  • Not using aliases for calculated columns, making results hard to read.

Summary

  • SELECT is the fundamental SQL command for retrieving data.
  • Specify columns explicitly for better performance and maintainability.
  • DISTINCT eliminates duplicates, aliases improve readability.
  • SELECT can include expressions and functions for data transformation.

Exercise

Write a query to select the `FirstName` and `LastName` columns from a `Customers` table.

SELECT FirstName, LastName FROM Customers;

Exercise Tips

  • Add WHERE clause to filter: SELECT FirstName, LastName FROM Customers WHERE City = 'New York'.
  • Use aliases: SELECT FirstName AS 'First Name', LastName AS 'Last Name' FROM Customers.
  • Add DISTINCT if needed: SELECT DISTINCT City FROM Customers.
  • Order results: SELECT FirstName, LastName FROM Customers ORDER BY LastName.

Code Editor

Output