DISTINCT, BETWEEN, IN & LIKE

-- DISTINCT: unique product categories ordered by a customer
SELECT DISTINCT product_category
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.user_id = 42;

-- DISTINCT ON (PostgreSQL only): keep one row per distinct value in given columns
SELECT DISTINCT ON (user_id)
    user_id, order_id, total_amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;   -- keeps latest order per user

-- BETWEEN (inclusive on both ends)
SELECT id, total_amount
FROM orders
WHERE total_amount BETWEEN 100.00 AND 500.00
  AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- IN: equivalent to multiple OR conditions (cleaner syntax)
SELECT id, email FROM users
WHERE status IN ('active', 'trial', 'grace_period');

-- NOT IN pitfall: if the list contains a NULL, entire result can be empty!
SELECT id FROM users
WHERE id NOT IN (1, 2, NULL);  -- returns 0 rows! NULL poisons NOT IN
-- Fix: use NOT EXISTS or filter NULLs from the list

-- LIKE: % = any chars, _ = exactly one char
SELECT id, email FROM users
WHERE email LIKE '%@gmail.com';      -- suffix match: full table scan!

SELECT id, sku FROM products
WHERE sku LIKE 'ELEC-%';             -- prefix match: CAN use a B-Tree index

-- ILIKE: case-insensitive LIKE (PostgreSQL only)
SELECT id, full_name FROM employees
WHERE full_name ILIKE 'john%';       -- matches John, JOHN, john

-- MySQL: LIKE is case-insensitive for case-insensitive collations (utf8mb4_general_ci)

-- Enabling fast substring search with pg_trgm (PostgreSQL)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- Now LIKE '%laptop%' or ILIKE '%laptop%' can use the GIN index
SELECT id, name FROM products WHERE name ILIKE '%laptop%';

-- REGEXP / SIMILAR TO for complex patterns (no index support)
SELECT id, phone FROM users WHERE phone ~ '^+91[0-9]{10}$';