SELECT Statements & Basic Queries

A basic SELECT statement follows this pattern:

1SELECT column1, column2, ...
2FROM table_name;

While this seems simple, SELECT can be expanded with many clauses to perform complex operations. Here's the full anatomy of a SELECT statement:

1SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2], ...
2FROM table_name
3[WHERE condition]
4[GROUP BY column(s)]
5[HAVING group_condition]
6[ORDER BY column(s) [ASC|DESC]]
7[LIMIT count [OFFSET skip]];

The clauses in square brackets are optional. We'll cover many of these in future lessons, but for today, let's focus on the first line:

SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2], ...

Key Components of a SELECT Statement:

1. SELECT keyword: Tells the database you want to retrieve data
2. Column list: Names the specific columns you want to retrieve
3. FROM clause: Specifies which table(s) to query
4. Semicolon: Marks the end of the SQL statement

The Execution Order

It's important to understand that SQL executes the clauses in a specific order, regardless of how you write them:

1. FROM: Identifies the table
2. WHERE: Filters rows (we'll cover this in Lesson 3)
3. GROUP BY: Groups rows (future lesson)
4. HAVING: Filters groups (future lesson)
5. SELECT: Returns specified columns
6. ORDER BY: Sorts the results (future lesson)
7. LIMIT: Restricts number of rows returned (future lesson)

Even though SELECT is the first word you write, it's actually processed near the end of the execution sequence!

When retrieving data, it's best practice to select only the columns you actually need, rather than using SELECT * (which selects all columns).

Benefits of selecting specific columns:

* Improved performance: Less data transferred and processed

* Reduced network traffic: Only necessary data moves across the network

* Clearer code: Makes it explicit which columns your application needs

* Future-proofing: Code won't break if columns are added to the table

Example:

Instead of:

1SELECT * FROM employees;

Prefer:

1SELECT employee_id, first_name, last_name, hire_date
2FROM employees;

Selecting Constants and Expressions

You can also select constants, calculations, and expressions:

1SELECT
2  employee_id,
3  first_name,
4  last_name,
5  salary,
6  salary * 1.1 AS increased_salary,
7  'Active' AS status,
8  CURRENT_DATE AS report_date
9FROM employees;

This query not only retrieves data from the table but also:

* Calculates a 10% salary increase

* Adds a constant 'Active' for each row

* Includes the current date

When to Use SELECT *

While selecting specific columns is best practice, there are some cases where SELECT * is acceptable:

* Exploratory data analysis (getting familiar with a table)

* Quick prototyping during development

* When you genuinely need all columns

* Ad-hoc queries during debugging

Just remember to replace it with specific column selections before going to production!

Column aliases let you rename columns in your result set, which is particularly useful when:

* Working with calculated fields that would otherwise have no name

* Making column names more readable or user-friendly

* Avoiding naming conflicts when working with multiple tables

* Creating reports with specific column headers

The AS keyword is used to create these aliases:

1SELECT
2  first_name AS "First Name",
3  last_name AS "Last Name",
4  hire_date AS "Start Date",
5  salary * 12 AS "Annual Salary"
6FROM employees;

AS is Optional

You can actually omit the AS keyword and still create aliases:

1SELECT
2  first_name "First Name",
3  last_name "Last Name",
4  salary * 12 "Annual Salary"
5FROM employees;

Both forms are valid, but including AS makes the code more readable.

Quoting Rules for Aliases

* Simple aliases (single word, no spaces, starts with a letter): Quotes optional

SELECT first_name AS FirstName

* Complex aliases (multiple words, spaces, special characters): Quotes required

SELECT first_name AS "First Name"

Different database systems use different quoting styles:

* MySQL & SQLite: Both single and double quotes work

* PostgreSQL: Double quotes for identifiers, single quotes for strings

* SQL Server: Square brackets can also be used first_name AS [First Name]

For maximum portability, use double quotes for aliases with spaces or special characters.

The DISTINCT keyword eliminates duplicate values from your results. It's placed right after the SELECT keyword:

1SELECT DISTINCT department
2FROM employees;

This returns a list of departments without duplicates. If the employees table has 1000 rows but only 10 different department values, this query will return just 10 rows.

DISTINCT with Multiple Columns

When using DISTINCT with multiple columns, it removes rows where the combination of all selected columns is duplicated:

1SELECT DISTINCT department, job_title
2FROM employees;

This returns unique department/job_title combinations.

Performance Considerations

DISTINCT requires additional processing because the database must sort or hash the result set to identify duplicates. For large tables, this can impact performance, so use it only when needed.

Common Use Cases for DISTINCT

1. Finding unique values

SELECT DISTINCT country FROM customers;

1. Data cleanup and validation

SELECT DISTINCT product_code FROM inventory; (Should match your product catalog)

1. Quick data profiling

SELECT COUNT(DISTINCT status) FROM orders; (How many different order statuses exist?)

1. Eliminating duplicate join results

When joining tables, you might get duplicate rows that need to be eliminated.

Alternative to DISTINCT: GROUP BY

For simple queries, DISTINCT is straightforward. For more complex scenarios, GROUP BY can be more efficient:

1SELECT department, job_title
2FROM employees
3GROUP BY department, job_title;

This produces the same unique combinations as the DISTINCT example above, but allows additional aggregate operations.

Well-formatted SQL is easier to read, debug, and maintain. Here are key principles for writing clean SQL:

1. Capitalize SQL Keywords

Capitalizing SQL keywords helps them stand out from column and table names:

1SELECT employee_id, first_name, last_name
2FROM employees
3WHERE department = 'Engineering';

2. Use Indentation and Line Breaks

Break long queries into multiple lines with consistent indentation:

1SELECT
2    e.employee_id,
3    e.first_name,
4    e.last_name,
5    d.department_name
6FROM
7    employees e
8JOIN
9    departments d ON e.department_id = d.department_id
10WHERE
11    e.hire_date > '2020-01-01'
12ORDER BY
13    e.last_name,
14    e.first_name;

3. Align Related Elements

Align related elements to make the structure clear:

1SELECT first_name     AS "First Name",
2       last_name      AS "Last Name",
3       department     AS "Department",
4       salary * 12    AS "Annual Salary",
5       hire_date      AS "Start Date"
6FROM   employees;

4. Use Meaningful Aliases

Give tables short but meaningful aliases, especially in complex queries:

1SELECT
2    emp.first_name,
3    emp.last_name,
4    dept.name AS department_name
5FROM
6    employees emp
7JOIN
8    departments dept ON emp.department_id = dept.id;

5. Add Comments for Complex Logic

Use -- for single-line comments or /* */ for multi-line comments:

1SELECT
2    employee_id,
3    salary,
4    -- Calculate bonus based on years of service
5    CASE
6        WHEN EXTRACT(YEAR FROM current_date) - EXTRACT(YEAR FROM hire_date) > 5 THEN salary * 0.15
7        ELSE salary * 0.10
8    END AS bonus
9FROM
10    employees;

6. Be Consistent with Your Style

Whether you prefer commas at the beginning or end of lines, or different indentation styles, consistency is key. Many teams have SQL style guides to ensure uniformity.

7. Use a SQL Formatter

For complex queries, consider using a SQL formatter tool to automatically apply consistent formatting. Many IDEs and SQL tools include built-in formatters.

Good formatting is not just for aesthetics—it reduces errors, speeds up code reviews, and makes complex queries easier to understand.