DISTINCT vs GROUP BY

Before (Inefficient Query)

-- Using DISTINCT on large result
SELECT DISTINCT customer_id, order_date
FROM orders 
WHERE status = 'completed';

After (Optimized Query)

-- Using GROUP BY with index
SELECT customer_id, order_date
FROM orders 
WHERE status = 'completed'
GROUP BY customer_id, order_date;

Why This Optimization Works

1. Similar Semanticsi: DISTINCT and GROUP BY with no aggregates are semantically equivalent in most cases.

2. Index Utilizationi: GROUP BY can use index-based sorting and grouping, while DISTINCT often requires a hash operation.

3. Optimizer Differencesi: Modern databases may optimize both similarly, but GROUP BY gives you more control over the execution plan.

With Aggregates

-- DISTINCT with aggregate (not valid in most DBs)
-- SELECT DISTINCT COUNT(*)

-- GROUP BY with aggregate (correct approach)
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

When to Use Each

Query Type	Use
Simple deduplication	Either
With aggregates	GROUP BY
Need ordering	GROUP BY
Complex expressions	GROUP BY

Practical Example

-- Get unique customers who placed orders
SELECT DISTINCT customer_id FROM orders;

-- Same result, potentially faster
SELECT customer_id FROM orders GROUP BY customer_id;

-- With additional aggregation
SELECT customer_id, COUNT(*) as orders
FROM orders
GROUP BY customer_id
ORDER BY orders DESC
LIMIT 10;

<br>

## COUNT(*) vs COUNT(column_name)

<br>

Use `COUNT(*)` when you want the total count of rows regardless of NULL values. Use `COUNT(column_name)` when you need to count only non-NULL values in that specific column.

```sql
-- COUNT(*): counts all rows including NULLs
SELECT COUNT(*) FROM orders;  -- Total number of orders

-- COUNT(column): counts only non-NULL values
SELECT COUNT(shipped_at) FROM orders;  -- Only orders that have been shipped

For SQLite specifically, COUNT(*) is generally faster than COUNT(column_name) because it can use optimized counting methods.

💡 For SQLite at least, COUNT(*) is generally faster. Test with EXPLAIN to confirm for your specific database.

WHERE vs HAVING

WHERE and HAVING perform similar functions but operate at different stages of query execution. The key difference is that WHERE operates before aggregation occurs, while HAVING operates after aggregation.

-- WHERE: Filters rows BEFORE aggregation (more efficient)
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

-- HAVING: Filters rows AFTER aggregation (required when filtering on aggregated values)
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

When to Use Each

Scenario	Use
Filter on non-aggregated column	WHERE
Filter on aggregated results	HAVING
Both possible	WHERE (more efficient)

ℹ️ If a filter can be applied before aggregation, always use WHERE. This reduces the number of rows that need to be aggregated, improving performance.