🔗 Advanced Joins = Complex Queries
Simple queries aren’t enough. Advanced join techniques handle complex data relationships and analytics.
📝 Complex Join Patterns
-- Multiple Joins with Aggregates
SELECT
c.name,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent,
AVG(o.total) as avg_order_value,
MAX(o.order_date) as last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name
HAVING total_spent > 1000
ORDER BY total_spent DESC;
-- Conditional Join
SELECT
u.name,
CASE
WHEN o.id IS NOT NULL THEN 'Has orders'
ELSE 'No orders'
END as order_status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.total > 100;
-- Join with Subquery
SELECT
u.name,
stats.order_count,
stats.total_spent
FROM users u
INNER JOIN (
SELECT
user_id,
COUNT(*) as order_count,
SUM(total) as total_spent
FROM orders
GROUP BY user_id
) stats ON u.id = stats.user_id
WHERE stats.total_spent > 1000;
-- Self Join for Hierarchical Data
SELECT
e1.name as employee,
e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
ORDER BY e1.name;
🎯 Performance Optimization
-- Index Strategy
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_products_category ON products (category);
-- Materialized View
CREATE MATERIALIZED VIEW customer_summary AS
SELECT
c.id,
c.name,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
-- Query Optimization
SELECT /*+ USE_HASH(customers orders) */
c.name,
COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01'
GROUP BY c.id, c.name;
-- Partition Pruning
SELECT *
FROM orders_partitioned
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01';
-- Index Only Scan
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total, order_date);
💡 Advanced Join Tips
- Use proper join types for your data
- Create covering indexes
- Use materialized views for performance
- Partition large tables
- Analyze query execution plans
Advanced join techniques unlock the full power of relational databases. They enable complex analytics and reporting.
