Joins and Window Functions

Hello and welcome! If you're just starting your SQL journey, you've probably heard about Joins and Window Functions. They sound complicated, but don't worry - I'll explain everything in simple terms with plenty of examples.

Part 1: Understanding Joins

What Are Joins?

Imagine you have two separate notebooks:

-** Notebook A**: Contains customer names and their IDs

• Notebook B: Contains customer orders with customer IDs

If you want to see "which customer ordered what," you need to connect these notebooks. That's exactly what Joins do, they connect tables based on common information (like customer ID).

A Real-Life Analogy

Think of joins like a wedding guest list:

Table 1: Guest names

Table 2: Meal preferences

The Join: Matching each guest to their meal choice using their name as the link

The Different Types of Joins
1. INNER JOIN - "Show me matches only."

INNER JOIN is like inviting only couples who both RSVP'd. It shows only records that exist in both tables.

Example Scenario: You want to see which customers have actually placed orders.

-- Find customers who have placed orders
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query shows ONLY customers who have at least one order. Customers without orders won't appear.

*2. LEFT JOIN *- "Show me everything from the left, and matching data from the right."

*LEFT JOIN * keeps ALL records from the first (left) table, and shows matching data from the second table. If there's no match, you see NULL (empty).

Example Scenario: You want to see ALL customers, even those who never ordered.

-- Show all customers and their orders (even if they have no orders)
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Now you'll see every customer. If a customer has no orders, the OrderDate column will be blank (NULL).

*3. RIGHT JOIN *- The Opposite of LEFT JOIN
Same as LEFT JOIN, but keeps ALL records from the right table.

-- Show all orders and their customers
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

4. FULL OUTER JOIN - "Show me everything, everywhere."
Shows ALL records from both tables, matching where possible.

-- Show all customers and all orders, regardless of matches
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

5. CROSS JOIN - "Combine everything with everything"
Creates all possible combinations between two tables.

-- Match every color with every product
SELECT Products.ProductName, Colors.ColorName
FROM Products
CROSS JOIN Colors;

Window Functions (Smart Calculations Without Losing Details)

What Are Window Functions?
Imagine you have a list of students and their test scores. You want to:

• See each student's score
• AND see the class average on the same row
• AND see each student's rank

With regular SQL, this is tricky. But with Window Functions, it's easy! Window functions let you perform calculations across rows while keeping all your original data visible.

OVER()

Every window function uses OVER(), think of it as saying "look at this group of rows."

1.RANK() - Who's on Top?
Example: Rank employees by salary within their department

SELECT 
    first_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_rank
FROM employees;

Simple Explanation:

PARTITION BY department

means "reset the ranking for each department"

ORDER BY salary DESC means "highest salary gets rank 1"

The result shows each person AND their rank in one row

Remember: Every SQL expert started exactly where you are now. The key is practice, curiosity, and building projects that interest you. Happy querying! that it forks !!