SELECT Statement

Master the SELECT statement to retrieve data from databases.

18 min read
Beginner

Introduction to SELECT

The SELECT statement is the most important and frequently used SQL command. It's how you retrieve (or "query") data from a database. Think of it as asking the database a question: "Show me this information."

Every time you search for something, view a list, or see data on a website, there's likely a SELECT statement running behind the scenes!

A SQL command used to retrieve data from one or more tables. It specifies which columns you want to see and which table to get them from.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name;

Let's explore SELECT with a realistic example. Here's our employees table:

employees
id
first_name
last_name
department
salary
hire_date
1AliceJohnsonEngineering750002022-01-15
2BobSmithMarketing650002021-06-20
3CharlieDavisEngineering850002020-03-10
4DianaWilsonSales700002022-08-01
5EveMartinezMarketing680002021-11-12

Selecting All Columns (SELECT *)

The simplest SELECT statement uses * (asterisk) to retrieve all columns from a table.

sql
SELECT * FROM employees;

*When to use SELECT :

  • Exploring a new table to see what columns it has
  • Quick debugging or testing
  • You genuinely need all columns

But be careful! In production code, it's better to specify exact columns (we'll see why next).

Tip: The semicolon (;) marks the end of a SQL statement. Always include it, especially when running multiple queries!

Selecting Specific Columns

Most of the time, you don't need all columns - just specific ones. List the column names you want, separated by commas.

sql
SELECT first_name, last_name, department
FROM employees;

*Why specify columns instead of using ?

  1. Performance: Fetching only needed columns is faster, especially with large tables
  2. Clarity: Makes your code self-documenting - others know exactly what data you need
  3. Maintainability: If the table structure changes (new columns added), your query still works
  4. Network efficiency: Less data transferred between database and application

Best Practice: Always specify column names in production code. Only use SELECT * for quick exploration or when you truly need every column.

Column Order and Multiple Columns

The order you list columns determines the order they appear in results. You can also select the same column multiple times!

sql
-- Columns appear in the order you specify
SELECT salary, first_name, last_name
FROM employees;

Selecting just one column:

sql
SELECT department FROM employees;

Notice that "Engineering" and "Marketing" appear multiple times. This is expected - SELECT returns all rows by default, including duplicates.

Using DISTINCT

To remove duplicate values and see only unique results, use the DISTINCT keyword.

sql
SELECT DISTINCT department
FROM employees;

DISTINCT with multiple columns returns unique combinations:

sql
SELECT DISTINCT department, hire_date
FROM employees;

Each row shows a unique combination of department and hire_date. Even though Engineering appears twice, the dates are different, so both rows are included.

Performance Note: DISTINCT requires the database to check for duplicates, which can be slow on large tables. Use it only when necessary!

Calculated Columns and Expressions

SELECT isn't limited to existing columns - you can perform calculations and create new values on the fly!

Arithmetic operations:

sql
SELECT first_name, salary, salary * 12 AS annual_salary
FROM employees;

The AS keyword creates a column alias (a temporary name). You can use any name you like!

String concatenation (combining text):

sql
SELECT first_name, last_name,
       CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

More calculations:

sql
SELECT first_name,
       salary,
       salary * 0.85 AS after_tax,
       salary * 0.15 AS tax_amount
FROM employees;

Column Aliases (AS Keyword)

Column aliases make your results more readable. They're especially useful for calculated columns or when column names are unclear.

Alias Syntax
Syntax
Description
Example
`column AS alias`Standard syntax (recommended)`salary AS monthly_pay`
`column alias`AS is optional`salary monthly_pay`
`"alias name"`Use quotes for spaces`salary AS "Monthly Salary"`
sql
SELECT first_name AS "First Name",
       department AS dept,
       salary AS "Monthly Salary"
FROM employees;

Tip: Aliases only affect how data is displayed in the result. They don't change the actual table or column names in the database.

Selecting Literal Values

You can include literal values (fixed text or numbers) in your SELECT statement. This is useful for adding context to results.

sql
SELECT first_name,
       'Employee' AS record_type,
       2024 AS report_year,
       department
FROM employees;

Every row gets the same literal value - "Employee" and 2024 in this case. This is helpful when combining data from different sources or adding metadata to reports.

Practical Example: Employee Report

Let's combine everything we've learned to create a comprehensive employee report:

sql
SELECT id AS employee_id,
       CONCAT(first_name, ' ', last_name) AS full_name,
       department,
       salary AS monthly_salary,
       salary * 12 AS annual_salary,
       YEAR(hire_date) AS year_hired
FROM employees;

This query demonstrates:

  • Column aliases for clarity
  • String concatenation (full name)
  • Mathematical calculations (annual salary)
  • Date functions (extracting year)
  • Professional formatting

Common Mistakes to Avoid

Don't make these mistakes:

  1. Missing comma between columns: SELECT first_name last_name ❌

    • Correct: SELECT first_name, last_name ✓

  2. Comma after last column: SELECT first_name, last_name, ❌

    • Correct: SELECT first_name, last_name ✓

  3. Missing FROM clause: SELECT first_name ❌

    • Correct: SELECT first_name FROM employees ✓

  4. Selecting non-existent columns: SELECT namee FROM employees ❌

    • Will cause an error! Check spelling carefully

  5. Using SELECT * in production: Slow and unpredictable ❌

    • Specify columns: SELECT id, name, email ✓

SELECT Query Checklist

Building a SELECT Query
Component
Required?
Purpose
Example
SELECT✅ YesSpecify columns to retrieve`SELECT first_name, salary`
FROM✅ YesSpecify which table`FROM employees`
DISTINCT❌ OptionalRemove duplicates`SELECT DISTINCT department`
AS❌ OptionalCreate column aliases`salary AS monthly_pay`
Calculations❌ OptionalPerform math/concatenation`salary * 12`

Key Takeaways

What you learned:

✅ SELECT retrieves data from tables ✅ **SELECT *** gets all columns (use sparingly!) ✅ Specify columns by listing them with commas ✅ DISTINCT removes duplicate values ✅ Calculations let you create new values on the fly ✅ AS creates readable column aliases ✅ Column order in SELECT determines display order

Next steps: In the upcoming lessons, we'll learn how to filter results with WHERE, sort data with ORDER BY, and limit the number of rows returned. The SELECT statement becomes even more powerful when combined with these tools!

Practice Exercise: Try writing SELECT statements to:

  1. Get all unique departments
  2. Calculate a 10% bonus for each employee
  3. Display full names with their annual salaries
  4. Show only first names and departments
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.