SELECT & Projections

The shape of SELECT

SELECT  <columns or expressions>
FROM    <table>;

That's it for the absolute minimum. Two real examples:

SELECT * FROM users;          -- all columns, all rows
SELECT name, email FROM users; -- only two columns

* is fine for ad-hoc exploration. In production code, always list the columns you want — it makes the query stable when the schema changes.

Column aliases — AS

Rename a column in the result set:

SELECT
  name        AS full_name,
  email       AS contact_email
FROM users;

AS is optional in most dialects (name full_name), but explicit is friendlier to read. Aliases are essential when:

• You have computed columns: SELECT price * 1.18 AS price_with_tax.
• You join tables that share column names: SELECT u.id AS user_id, p.id AS post_id.
• The original name is awkward: SELECT created_at AS "Created".

Expressions in SELECT

You can compute things in the SELECT list:

SELECT
  name,
  age,
  age * 12        AS age_in_months,
  age >= 18       AS is_adult,
  'Hello, ' 
name AS greeting   --
 
is string concat (Postgres / SQLite)
FROM users;

Maths, comparisons, and string operators all work inline. (MySQL uses CONCAT(a, b) instead of

COALESCE is a star — returns the first non-NULL value:

SELECT
  name,
  COALESCE(nickname, name) AS display_name
FROM users;

CASE is the SQL equivalent of if/else:

SELECT
  name,
  CASE
    WHEN age < 18  THEN 'minor'
    WHEN age < 65  THEN 'adult'
    ELSE              'senior'
  END AS life_stage
FROM users;

DISTINCT — drop duplicates

SELECT DISTINCT country FROM customers;

Returns each distinct country once. Works on multiple columns too:

SELECT DISTINCT country, city FROM customers;

→ each unique (country, city) pair.

⚠️ DISTINCT works on the whole row of the SELECT list, not on the first column. SELECT DISTINCT a, b does not mean "distinct a, then any b".

SELECT without a FROM

Most engines let you SELECT a literal expression for testing:

SELECT 1 + 1;                -- 2
SELECT NOW();                -- current timestamp
SELECT UPPER('hello');       -- 'HELLO'
SELECT 'pi ≈ ' || 3.14;       -- 'pi ≈ 3.14'

Useful while learning — and for testing functions before plugging them into a real query.

Comments & formatting

Two comment styles work in every major engine:

-- single line
SELECT name FROM users; -- end-of-line is fine too
/* multi
   line */
SELECT email FROM users;

Formatting tips that pay off forever:

-- ✅ One column per line for long SELECTs
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name, u.email;
-- ❌ One giant line you'll regret in three months
SELECT u.id,u.name,u.email,COUNT(p.id) AS post_count FROM users u LEFT JOIN posts p ON p.user_id=u.id GROUP BY u.id,u.name,u.email;

Common gotchas

• Don't SELECT * in code that ships. Schema changes silently break things.
• An alias defined in SELECT can't be used in WHERE (because of execution order). Repeat the expression or wrap in a subquery.
• String quotes are single quotes. Double quotes mean *identifier* in standard SQL.
• NULL is not equal to anything, even NULL. Use IS NULL / IS NOT NULL (covered in WHERE).
• Column order in SELECT doesn't have to match table order. Mix and reorder freely.

SELECT
  DISTINCT
  COALESCE(nickname, name) AS display_name,
  age * 12                 AS age_months,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS bracket
FROM users;