Bits of .NET

🔗 Joins = Data Relationships

Data is in multiple tables. Joins combine them. INNER, LEFT, RIGHT, FULL — choose right.

📝 Join Types

-- INNER JOIN (Only matching)
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (All from left)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Users with no orders → NULL

-- RIGHT JOIN (All from right)
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL JOIN (All from both)
SELECT u.name, o.total
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN (Cartesian product)
SELECT u.name, p.name
FROM users u
CROSS JOIN products p;

🎯 Advanced Joins

-- Multiple joins
SELECT 
    u.name,
    o.total,
    p.name as product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- Self join (employee manager)
SELECT 
    e.name as employee,
    m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Join with aggregate
SELECT 
    u.name,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Join with subquery
SELECT u.name, stats.order_count
FROM users u
INNER JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) stats ON u.id = stats.user_id;

-- Multiple conditions
SELECT u.name, o.total
FROM users u
INNER JOIN orders o 
    ON u.id = o.user_id 
    AND o.total > 100
WHERE u.active = 1;

-- Join Tips
- Use INNER JOIN for matching data
- Use LEFT JOIN when you need all from left
- Use JOINs instead of subqueries for performance
- Index join columns
- Be careful with multiple joins

💡 Join Tips

• Use INNER JOIN for matching data
• Use LEFT JOIN when you need all from left
• Use JOINs instead of subqueries for performance
• Index join columns
• Be careful with multiple joins