SELECT Queries Basics

Introduction

SELECT reads data from tables—the statement you will run most often. This chapter covers column lists, WHERE filters, sorting, pagination, DISTINCT, PostgreSQL's DISTINCT ON, and aliases. Syntax largely matches MySQL SELECT basics; differences are called out where they matter.

Prerequisites

• Insert and COPY
• Sample rows in users and posts

Select Columns

All columns (fine for exploration):

SELECT * FROM users;

Specific columns (prefer in production):

SELECT id, email, display_name FROM users;

Code explanation:

• Listing columns reduces I/O and avoids surprise breakage when new columns appear

WHERE Filters

Comparison operators:

SELECT id, display_name FROM users WHERE id = 1;
 
SELECT title FROM posts WHERE user_id = 2;
 
SELECT * FROM posts WHERE published_at IS NOT NULL;

Logical combinations:

SELECT title, published_at
FROM posts
WHERE user_id = 1
  AND published_at IS NOT NULL;

SELECT email FROM users
WHERE display_name = 'Ada Lo' OR display_name = 'Bob Chen';

Negation:

SELECT title FROM posts WHERE published_at IS NULL;

BETWEEN, IN, LIKE

Range:

SELECT title, published_at
FROM posts
WHERE published_at BETWEEN '2026-05-01' AND '2026-05-31';

Set membership:

SELECT display_name FROM users
WHERE id IN (1, 2, 3);

Pattern match (case-sensitive by default):

SELECT title FROM posts WHERE title LIKE 'Hello%';
 
SELECT email FROM users WHERE email LIKE '%@example.com';

Case-insensitive match:

SELECT email FROM users WHERE email ILIKE '%@EXAMPLE.COM';

Code explanation:

• ILIKE is PostgreSQL-specific—MySQL often uses LOWER(email) LIKE ...
• Leading % in LIKE may prevent index use—acceptable on small tables

IS NULL

SELECT id, title FROM posts WHERE published_at IS NULL;

Use IS NULL / IS NOT NULL—never = NULL (unknown in SQL).

ORDER BY

Sort ascending (default) or descending:

SELECT id, title, published_at
FROM posts
ORDER BY published_at DESC NULLS LAST, id ASC;

Code explanation:

• NULLS FIRST / NULLS LAST control where nulls sort—MySQL lacks this explicit control
• Stable sorts: add a unique column like id as tiebreaker

LIMIT and OFFSET Pagination

First page (newest published posts):

SELECT id, title, published_at
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Second page:

SELECT id, title, published_at
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10 OFFSET 10;

DISTINCT

Remove duplicate values:

SELECT DISTINCT user_id FROM posts;

Distinct pairs:

SELECT DISTINCT user_id, published_at IS NOT NULL AS is_published
FROM posts;

DISTINCT ON (PostgreSQL)

Pick one row per group—first row after ORDER BY wins:

-- Latest published post per user
SELECT DISTINCT ON (user_id)
  user_id,
  id AS post_id,
  title,
  published_at
FROM posts
WHERE published_at IS NOT NULL
ORDER BY user_id, published_at DESC;

Code explanation:

• DISTINCT ON (user_id) requires user_id to lead the ORDER BY (or match leftmost sort keys)
• MySQL has no DISTINCT ON—workarounds use window functions or GROUP BY with aggregates

Aliases

Column alias:

SELECT
  email AS user_email,
  created_at AS joined_at
FROM users;

Table alias:

SELECT u.id, u.email, p.title
FROM users u
JOIN posts p ON p.user_id = u.id;

Expression alias:

SELECT
  id,
  title,
  LENGTH(body) AS body_length
FROM posts;

AS is optional but improves readability.

Array and JSONB in SELECT

Unnest tags:

SELECT id, title, unnest(tags) AS tag
FROM posts
WHERE tags IS NOT NULL AND tags <> '{}';

JSONB field:

SELECT id, title, metadata ->> 'lang' AS lang
FROM posts
WHERE metadata ? 'pinned';

Deep JSONB querying is covered in JSONB and Advanced Types.

Practice Queries

Active users who published at least one post:

SELECT DISTINCT u.id, u.display_name
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.is_active = TRUE
  AND p.published_at IS NOT NULL
ORDER BY u.display_name;

Draft posts (unpublished):

SELECT u.display_name, p.title
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NULL
ORDER BY p.id;

FAQ

SELECT * in production?

Avoid in application code—explicit columns prevent leaks and reduce bandwidth when schemas evolve.

ILIKE vs LIKE?

LIKE is case-sensitive; ILIKE is case-insensitive. For indexed search on text, consider pg_trgm or full-text search later.

DISTINCT vs DISTINCT ON?

DISTINCT deduplicates full rows or listed columns. DISTINCT ON (expr) keeps one row per distinct expr value—powerful but PostgreSQL-only.

Why is OFFSET slow?

The server still walks skipped rows. Use keyset pagination for infinite scroll at scale.

Are MySQL and PostgreSQL SELECT identical?

Mostly—watch ILIKE, DISTINCT ON, NULLS FIRST/LAST, and boolean literals (TRUE / FALSE vs 1 / 0).

How do I explain a slow SELECT?

Use EXPLAIN ANALYZE—Indexes and EXPLAIN ANALYZE.