SELECT Basics: Mastering Data Retrieval

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- The simplest possible SELECT statement
SELECT 1;
-- Returns: 1 (a single row with a single column containing the value 1)
 
-- SELECT with a constant expression
SELECT 'Hello, SQL World!';
-- Returns: Hello, SQL World!
 
-- The canonical form: SELECT from a table
SELECT column_name FROM table_name;
 
-- A complete example with real semantics
SELECT 
    employee_id,      -- Retrieve the employee_id column
    first_name,       -- Retrieve the first_name column  
    last_name,        -- Retrieve the last_name column
    hire_date         -- Retrieve the hire_date column
FROM 
    employees;        -- From the employees table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Basic column selection
SELECT employee_id, first_name, last_name
FROM employees;
 
-- Using DISTINCT to remove duplicates
SELECT DISTINCT department_id
FROM employees;
 
-- Column aliasing with AS keyword
SELECT 
    first_name AS given_name,
    last_name AS family_name,
    salary AS annual_compensation
FROM employees;
 
-- Column aliasing without AS (valid but less readable)
SELECT 
    first_name given_name,
    last_name family_name
FROM employees;
 
-- Aliasing with quoted identifiers for special characters/spaces
SELECT 
    first_name AS "First Name",
    last_name AS "Family Name",
    salary AS "Annual Salary (USD)"
FROM employees;
 
-- Table-qualified column names (essential for joins)
SELECT 
    employees.employee_id,
    employees.first_name,
    departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
 
-- Using table aliases for brevity
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- This query demonstrates why processing order matters
 
SELECT 
    department_id,
    COUNT(*) AS employee_count,       -- Alias defined in step 5
    AVG(salary) AS average_salary     -- Alias defined in step 5
FROM employees
WHERE hire_date > '2020-01-01'        -- Step 2: Cannot use employee_count here!
GROUP BY department_id                 -- Step 3: Groups are formed
HAVING COUNT(*) > 5                    -- Step 4: Must use COUNT(*), not alias
ORDER BY average_salary DESC;          -- Step 7: CAN use alias (some databases)
 
-- INCORRECT: Trying to use SELECT alias in WHERE
-- SELECT salary * 12 AS annual_salary
-- FROM employees
-- WHERE annual_salary > 50000;  -- ERROR! annual_salary doesn't exist yet
 
-- CORRECT: Repeat the expression in WHERE
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 50000;  -- Use the original expression
 
-- OR use a subquery/CTE
SELECT * FROM (
    SELECT employee_id, salary * 12 AS annual_salary
    FROM employees
) AS subq
WHERE annual_salary > 50000;  -- Now it works!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Example table: products
-- | product_id | name        | category    | price  |
-- |------------|-------------|-------------|--------|
-- | 1          | Widget A    | Electronics | 29.99  |
-- | 2          | Widget B    | Electronics | 49.99  |
-- | 3          | Gadget X    | Electronics | 29.99  |
-- | 4          | Tool Y      | Hardware    | 15.00  |
-- | 5          | Tool Z      | Hardware    | 15.00  |
 
-- Query 1: Returns all columns with original names
SELECT product_id, name, category, price
FROM products;
-- Result columns: product_id, name, category, price
 
-- Query 2: Column order matches SELECT list ORDER
SELECT price, name, product_id
FROM products;
-- Result columns: price, name, product_id  (different order!)
 
-- Query 3: Expressions derive their types
SELECT 
    name,
    price,
    price * 1.1 AS price_with_tax,     -- Numeric type (DECIMAL)
    CONCAT(name, ' - ', category),      -- String type (VARCHAR)
    price > 20 AS is_expensive          -- Boolean type (in supporting DBs)
FROM products;
 
-- Query 4: Demonstrating duplicate handling
SELECT category FROM products;
-- Returns: Electronics, Electronics, Electronics, Hardware, Hardware
-- (5 rows with duplicates)
 
SELECT DISTINCT category FROM products;
-- Returns: Electronics, Hardware
-- (2 rows, duplicates removed)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Source column name preservation
SELECT first_name FROM employees;
-- Result column name: first_name
 
-- Explicit aliasing overrides source name
SELECT first_name AS given_name FROM employees;
-- Result column name: given_name
 
-- Expression without alias: generated name (DBMS-dependent)
SELECT UPPER(first_name) FROM employees;
-- MySQL:      UPPER(first_name)
-- PostgreSQL: upper
-- SQL Server: (No column name)
-- Oracle:     UPPER(FIRST_NAME)
 
-- Always alias expressions for consistent, readable results
SELECT UPPER(first_name) AS upper_first_name FROM employees;
-- Result column name: upper_first_name (consistent across all DBMS)
 
-- Handling column name conflicts in joins
SELECT 
    employees.name,      -- Qualified: employees.name
    departments.name     -- Qualified: departments.name
FROM employees
JOIN departments ON employees.department_id = departments.id;
-- PROBLEM: Both columns named "name" in result - ambiguous!
 
-- Solution: Use aliases to disambiguate
SELECT 
    employees.name AS employee_name,
    departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
-- Clear result: employee_name, department_name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- NULL in source data propagates to results
-- Table: employees
-- | id | name  | manager_id | commission |
-- |----|-------|------------|------------|
-- | 1  | Alice | NULL       | 1000       |
-- | 2  | Bob   | 1          | NULL       |
-- | 3  | Carol | 1          | 500        |
 
SELECT name, manager_id, commission
FROM employees;
-- Alice has NULL manager_id (she's top-level)
-- Bob has NULL commission (no commission data)
 
-- NULL propagation in expressions
SELECT 
    name,
    commission,
    commission * 2 AS double_commission
FROM employees;
-- For Bob: commission * 2 = NULL * 2 = NULL
-- Any arithmetic with NULL produces NULL
 
-- NULL in string concatenation
SELECT 
    name,
    CONCAT('Manager: ', manager_id) AS manager_info  -- Varies by DBMS
FROM employees;
-- In standard SQL: NULL concatenation = NULL
-- MySQL CONCAT: NULL becomes empty string (non-standard!)
-- Use COALESCE for safety:
SELECT 
    name,
    CONCAT('Manager: ', COALESCE(CAST(manager_id AS CHAR), 'None')) AS manager_info
FROM employees;
 
-- NULL affects DISTINCT processing
SELECT DISTINCT manager_id FROM employees;
-- Returns: NULL, 1
-- Multiple NULLs are considered equal for DISTINCT purposes
 
-- Counting NULL vs non-NULL
SELECT 
    COUNT(*) AS total_rows,              -- Counts all rows: 3
    COUNT(commission) AS with_commission, -- Counts non-NULL: 2
    COUNT(manager_id) AS with_manager     -- Counts non-NULL: 2
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- POOR: Selects all columns when only id and name needed
SELECT * FROM employees WHERE department_id = 5;
-- Reads: id, name, email, phone, address, hire_date, salary, bio, photo...
 
-- BETTER: Select only needed columns
SELECT employee_id, first_name, last_name 
FROM employees 
WHERE department_id = 5;
-- Reads: only 3 columns, potentially from covering index
 
-- POOR: Unnecessary DISTINCT with primary key
SELECT DISTINCT employee_id, first_name FROM employees;
-- employee_id is unique, so DISTINCT does nothing but add sorting cost
 
-- BETTER: Remove unnecessary DISTINCT
SELECT employee_id, first_name FROM employees;
 
-- POOR: Expensive function call on every row
SELECT 
    employee_id,
    (SELECT department_name FROM departments d 
     WHERE d.id = e.department_id) AS dept_name  -- Scalar subquery per row!
FROM employees e;
 
-- BETTER: Use JOIN instead of scalar subquery
SELECT 
    e.employee_id,
    d.department_name AS dept_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
 
-- CONSIDERATION: Covering index optimization
-- If index exists on (department_id, first_name, last_name):
SELECT first_name, last_name 
FROM employees 
WHERE department_id = 5;
-- Can be served entirely from the index without table access