Day 36: Self Joins and Subqueries in MySQL

Today, let us explore Self Joins and Subqueries in MySQL, two powerful techniques for handling complex data relationships!

1. Self Join - Joining a Table with Itself

A table is joined with itself, treating it as two separate tables using aliases

Example: Finding Employees and Their Managers

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;        

Returns each employee's manager's name from the same table

Example: Finding Customers Who Referred Other Customers

SELECT c1.name AS Customer, c2.name AS Referred_Customer
FROM customers c1
JOIN customers c2 ON c1.customer_id = c2.referred_by;        

Returns who referred whom in a customer referral program

2. Subqueries - Querie Inside Queries

A Subquery is a query inside another query. It is used when we need intermediate results for filtering, aggregation, or computation.

Example: Getting Employees Who Earn Above the Company's Average Salary

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);        

Returns employees earning more than the average salary

Example: Fetching Customers Who Placed an Order

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);        

Example: Finding Products That Have Sold More Than 100 Units

SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM sales WHERE quantity >100);        

Shows products with more than 100 sales

#100DaysOfCode #MySQL #SQL #Database #DataAnalysis #Learning #Python #BackendDevelopment