DISTINCT, IN, BETWEEN, LIKE

~10 min · queries, operators

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The shorthand operators

Four operators that turn long, ugly conditions into short, readable ones. Memorise the syntax once; you'll use them daily.

DISTINCT — kill duplicates

SELECT DISTINCT category FROM products returns each category once. SELECT DISTINCT a, b FROM t returns each unique combination. COUNT(DISTINCT col) counts unique values.

IN, BETWEEN, LIKE/ILIKE

IN (a, b, c) is shorthand for = a OR = b OR = c, plus subquery support. BETWEEN x AND y is inclusive on both ends. LIKE uses % (zero or more chars) and _ (one char); ILIKE is case-insensitive (PostgreSQL extension).

Code

DISTINCT shapes·sql

SELECT DISTINCT category FROM products;

SELECT DISTINCT city, country FROM customers ORDER BY country, city;

SELECT COUNT(DISTINCT category) AS unique_categories FROM products;

-- DISTINCT ON (PostgreSQL extension): one row per group, defined by ORDER BY
SELECT DISTINCT ON (customer_id) customer_id, placed_at, total
FROM   orders
ORDER  BY customer_id, placed_at DESC;
-- Returns the most recent order per customer.

IN, BETWEEN, LIKE/ILIKE·sql

SELECT * FROM orders
WHERE  status IN ('pending', 'processing', 'shipped');

SELECT * FROM events
WHERE  event_date BETWEEN '2025-01-01' AND '2025-12-31';

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM articles WHERE title ILIKE '%postgres%';

-- IN with subquery
SELECT * FROM products
WHERE  category_id IN (SELECT id FROM categories WHERE department = 'Electronics');

External links

PostgreSQL DISTINCT ON

Exercise

Use DISTINCT ON to get the latest order per customer. Then rewrite the same query with ROW_NUMBER() in a subquery (you'll see this pattern in lesson 14). Compare them.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousCASE, COALESCE, and Conditional Logic Next →INNER JOIN: Stitching Tables Together

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.