SQL for Developers — Queries, Joins, and Optimization

Basic Queries

-- Select specific columns
SELECT name, email FROM users WHERE active = true;

-- Filter with conditions
SELECT * FROM products WHERE price > 50 AND category = 'electronics';

-- Sorting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Pagination
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 40;

Joins

-- INNER JOIN: Only matching records
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN: All from left, matching from right
SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

-- Multiple joins
SELECT u.name, p.title, c.body
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

Aggregation

-- Group by with aggregation
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

-- Subqueries
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Indexes

-- Create index for faster queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Composite index
CREATE INDEX idx_products_cat_price ON products(category, price);