SQL Programming Resources

⚠️ 23. Common Subquery Mistakes Mistake 1 — Returning multiple rows with = Use IN instead of = when multiple rows are expected. Mistake 2 — Forgetting NULL behavior with NOT IN Mistake 3 — Ignoring duplicates Mistake 4 — Making the query unnecessarily complicated 🎤 SQL Interview Questions Q1. What is a subquery? A query nested inside another query. Q2. What is a scalar subquery? A subquery that returns a single value. Q3. When should you use IN? When the subquery returns a set of values. Q4. What does EXISTS do? It checks whether at least one matching row exists. Q5. What is a correlated subquery? A subquery that references a column from the outer query. Q6. What is a derived table? A subquery in the FROM clause treated as a temporary result set. Q7. Can a subquery be used in SELECT? Yes. Q8. What is the difference between IN and EXISTS? IN compares against a set, while EXISTS checks for existence. Q9. Why is NOT IN dangerous with NULL? Three-valued logic can cause unexpected results. Q10. Can every subquery be replaced with a JOIN? Many can, but the best approach depends on the logic. 📝 Practice Questions Practice 1 — Employees earning more than average:

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

Practice 2 — Customers with at least one order:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Practice 3 — Customers with more than 5 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

Practice 4 — Products higher than average price:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Practice 5 — Customers who never placed an order:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🧪 Mini SQL Challenge Find customers whose total spending is greater than the average customer spending.

SELECT customer_id, total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > (
    SELECT AVG(total_spending)
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_spending
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

💡 Double Tap ❤️ For More ----- 0.118646 ₽ · /balance_help

Means: Return customers for whom no matching order exists. 🧠 12. EXISTS vs IN IN → Compare against a set of returned values EXISTS → Check whether a matching row exists 🔄 13. Correlated Subquery Depends on the current row of the outer query.

SELECT
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

«Is this employee's salary higher than the average salary of their own department?» 🏢 14. Above-Department-Average Salary This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average. 📦 15. Subquery in FROM It can appear in the FROM clause.

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales;

This is often called a derived table. 📈 16. Finding High-Value Customers

SELECT
    customer_id,
    total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;

🧩 17. Subquery in SELECT

SELECT
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers c;

⚠️ 18. But Be Careful with Correlated Subqueries Instead of a correlated subquery in SELECT, you could use:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

The better choice depends on the database optimizer, table size, indexes, and other factors. 🧱 19. Nested Subqueries

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id IN (
        SELECT category_id
        FROM categories
        WHERE category_name = 'Electronics'
    )
);

Deeply nested queries can become difficult to read. Use CTEs instead. 🧠 20. Subqueries vs JOINs Subquery:

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

JOIN:

SELECT DISTINCT
    c.*
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Choose based on readability, business logic, and performance. 🏆 21. Subqueries for Business Analysis Find products above average:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Find customers with at least one order:

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Find customers with more than 10 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 10
);

🧮 22. Subquery for KPI Comparison

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

🚀 SQL Roadmap 2026 — Part 12 🧩 SQL Subqueries — Using One Query Inside Another So far, we've learned how to retrieve, filter, group, transform, and combine data. But sometimes a business question requires one query to use the result of another query. For example: «Find employees whose salary is higher than the average salary.» First, we need to calculate:

SELECT AVG(salary)
FROM employees;

Then compare every employee against that result. A subquery allows us to do both inside one SQL statement. 🧠 1. What Is a Subquery? A subquery is a SQL query written inside another SQL query.

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query:

SELECT AVG(salary) FROM employees

calculates the average salary. The outer query then uses that result. Think of it as: Outer Query → Needs an answer → Subquery calculates the answer → Outer Query uses it 🔹 2. Basic Subquery Structure

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT ...
);

The inner query is enclosed in ( ... ). 📊 3. Subquery Returning One Value A scalar subquery returns a single value.

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

The inner query returns something like 65000. The outer query then finds employees earning more than 65000. 💰 4. Employees Earning Above Average Classic interview question.

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

Logic: Calculate average salary → Compare every employee → Keep salary > average 🏆 5. Products More Expensive Than Average

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

🔢 6. Subquery with COUNT() Customers who have placed more than 5 orders:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

🔗 7. Subquery with IN Useful when a subquery returns multiple values.

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

Returns customers who have at least one order. ⚠️ 8. Single Value vs Multiple Values One value → Use =, >, <, >=, <=

WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

Multiple values → Use IN

WHERE customer_id IN (
    SELECT customer_id
    FROM orders
)

🚫 9. NOT IN

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

⚠️ Important NULL Warning: NOT IN can produce unexpected results if the subquery contains NULL. For anti-matching, NOT EXISTS is often safer. 🔎 10. EXISTS

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Means: Return the customer if at least one matching order exists. 🚫 11. NOT EXISTS

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥 Explore these FREE certification courses in today’s most in-demand technology fields: 📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF 💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo 💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q ☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn 🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv 🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH ⚡ Start learning today and prepare yourself for better career opportunities in 2026!

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝘁𝗼 𝗚𝗲𝘁 𝗮 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯 𝗶𝗻 𝟮𝟬𝟮𝟲 📊 Build job-ready skills through live online classes, practical assignments and real-world projects. 💼 End-to-End Placement Support 🤝 500+ Partner Companies 🎓 2000+ Students Placed 🏆 Highest Salary: ₹41 LPA 📞 Get FREE career counselling and check your eligibility! 🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 https://pdlink.in/45vk5ph ⚡Prepare for roles such as Data Analyst, Business Analyst, BI Analyst and Reporting Analyst.

🚀 𝗧𝗼𝗽 𝟯 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 🔥 💫 Artificial Intelligence (AI) 📊 Data Analytics 🔐 Cybersecurity 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/4y2XyN1 🎯 Perfect for Students • Freshers • Beginners • Tech Enthusiasts 💡 Learn for FREE → Build Skills → Upgrade Your Career

𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰: Join for more: https://t.me/sqlanalyst 1. Danny’s Diner: Restaurant analytics to understand the customer orders pattern. Link: https://8weeksqlchallenge.com/case-study-1/ 2. Pizza Runner Pizza shop analytics to optimize the efficiency of the operation Link: https://8weeksqlchallenge.com/case-study-2/ 3. Foodie Fie Subscription-based food content platform Link: https://lnkd.in/gzB39qAT 4. Data Bank: That’s money Analytics based on customer activities with the digital bank Link: https://lnkd.in/gH8pKPyv 5. Data Mart: Fresh is Best Analytics on Online supermarket Link: https://lnkd.in/gC5bkcDf 6. Clique Bait: Attention capturing Analytics on the seafood industry Link: https://lnkd.in/ggP4JiYG 7. Balanced Tree: Clothing Company Analytics on the sales performance of clothing store Link: https://8weeksqlchallenge.com/case-study-7 8. Fresh segments: Extract maximum value Analytics on online advertising Link: https://8weeksqlchallenge.com/case-study-8

𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰: Join for more: https://t.me/sqlanalyst 1. Danny’s Diner: Restaurant analytics to understand the customer orders pattern. Link: https://8weeksqlchallenge.com/case-study-1/ 2. Pizza Runner Pizza shop analytics to optimize the efficiency of the operation Link: https://8weeksqlchallenge.com/case-study-2/ 3. Foodie Fie Subscription-based food content platform Link: https://lnkd.in/gzB39qAT 4. Data Bank: That’s money Analytics based on customer activities with the digital bank Link: https://lnkd.in/gH8pKPyv 5. Data Mart: Fresh is Best Analytics on Online supermarket Link: https://lnkd.in/gC5bkcDf 6. Clique Bait: Attention capturing Analytics on the seafood industry Link: https://lnkd.in/ggP4JiYG 7. Balanced Tree: Clothing Company Analytics on the sales performance of clothing store Link: https://8weeksqlchallenge.com/case-study-7 8. Fresh segments: Extract maximum value Analytics on online advertising Link: https://8weeksqlchallenge.com/case-study-8

𝗧𝗼𝗽 𝟱 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗮𝗿𝗲𝗲𝗿 📊 Want to start a career in Data Science without spending money? Here are 5 beginner-friendly learning resources covering essential skills such as Python, SQL, Machine Learning and hands-on projects. 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/4ilAmok 🎯 Perfect for Students • Freshers • Beginners • Aspiring Data Scientists 💡 Learn → Practice → Build Projects → Create Your Portfolio

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍 💫Accelerate your career in Data Science 💫Discover the skills, tools and career roadmap needed to enter this high-demand field. 🔥 Beginner-friendly online session—no prior experience required! 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/46adC3l (Only few slots left ) 📅 Date: September 11, 2026 ⏰ Time: 7:00 PM

🚀 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗟𝗮𝗻𝗱 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯𝘀 𝗶𝗻 𝟮𝟬𝟮𝟲😍 💰 Highest Salary: ₹41 LPA 📈 Average Salary: ₹7.4 LPA 🎓 2,000+ Students Placed 🏢 500+ Hiring Partners 💻 Full Stack :- https://pdlink.in/3SuUeuD 📊 Data Analytics :- https://pdlink.in/45vk5ph 💫AI Engineering :- https://pdlink.in/4fWJVID 🔥 Take the first step towards your high-paying tech career in 2026!

⚠️ 20. Double Counting in Multiple JOINs If both "orders" and "payments" have multiple rows per customer, joining them directly can create a many-to-many multiplication. Example: 2 orders × 3 payments = 6 joined rows. SUM() will overcount. Understand the grain of each table before joining. 🧠 21. JOINs and Table Grain Before writing a JOIN, identify: Table 1 - One row = one customer, Table 2 - One row = one order → One-to-Many relationship. Understanding table grain helps prevent: duplicate counts, inflated revenue, incorrect averages, incorrect KPIs. 🎤 SQL Interview Questions Q1. What is a JOIN? Combines rows from multiple tables using a related condition. Q2. What is the difference between INNER JOIN and LEFT JOIN? INNER returns only matching, LEFT returns all from left + matching from right. Q3. How do you find customers who never placed an order? LEFT JOIN + WHERE o.customer_id IS NULL Q4. What is a SELF JOIN? Joins a table to itself, for hierarchical relationships. Q5. What is a CROSS JOIN? Creates every possible combination. Q6. Why can JOINs create duplicate rows? Because of one-to-many or many-to-many relationships. Q7. Why should you understand table grain? Because grain determines how rows multiply and whether aggregations become inaccurate. Q8. What happens when there is no match in a LEFT JOIN? Columns from right become NULL. Q9. How do you count unique customers after a JOIN? COUNT(DISTINCT customer_id) Q10. Can a query contain multiple JOINs? Yes. 📝 Practice Questions Practice 1: Return customer names and their orders.

SELECT c.customer_name, o.order_id 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

Practice 2: Find customers who have never ordered.

SELECT c.customer_id, c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

Practice 3: Calculate total spending per customer.

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 4: Return all customers and their order counts, including zero orders.

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 5: Find number of unique customers who placed orders.

SELECT COUNT(DISTINCT c.customer_id) AS unique_customers 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

🧪 Mini SQL Challenge Write a query that returns: Customer name, Product name, Category, Amount - Only orders > ₹1,000. Solution:

SELECT c.customer_name, p.product_name, p.category, o.amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN products p ON o.product_id = p.product_id 
WHERE o.amount > 1000 
ORDER BY o.amount DESC;

📌 JOINs are the bridge between database tables. But writing a JOIN is only half the skill. A strong Data Analyst also understands: What each table represents → How tables are related → How rows will multiply → How that affects the KPI. Double Tap ❤️ For More ----- 1.64 ₽ · /balance_help

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
INNER JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

💰 11. Include Customers with Zero Spending

SELECT c.customer_id, c.customer_name, COALESCE(SUM(o.amount), 0) AS total_spending 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

🔢 12. JOIN + COUNT()

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Why COUNT(o.order_id) instead of COUNT(*)? Because COUNT(*) would count the LEFT JOIN row even when the customer has no matching order. ⚠️ 13. A Very Common JOIN Mistake

SELECT ... WHERE o.amount > 500; -- This removes NULLs and behaves like INNER JOIN

Correct:

LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.amount > 500;

Important concept: With an OUTER JOIN, the location of a filter can change the result. 🔗 14. Joining More Than Two Tables

SELECT c.customer_name, o.order_id, p.product_name, o.amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN products p ON o.product_id = p.product_id;

🏢 15. Real-World Business Example

SELECT p.category, SUM(o.amount) AS total_revenue 
FROM orders o 
JOIN products p ON o.product_id = p.product_id 
GROUP BY p.category 
ORDER BY total_revenue DESC;

This is a typical Data Analyst query. 📈 16. JOIN + WHERE + GROUP BY + HAVING Question: «Find customers who spent more than ₹50,000.»

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name 
HAVING SUM(o.amount) > 50000 
ORDER BY total_spending DESC;

Logical flow: JOIN → GROUP BY → HAVING → ORDER BY 🪞 17. SELF JOIN A table can also be joined to itself.

SELECT e.employee_name AS employee, m.employee_name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.employee_id;

🔢 18. CROSS JOIN "CROSS JOIN" produces every possible combination of rows. 5 products x 4 regions = 20 rows 🚨 19. The Biggest JOIN Problem: Duplicate Rows One customer has five orders → customer appears five times. This is the natural result of a one-to-many relationship. If you want unique customers:

SELECT COUNT(DISTINCT c.customer_id)

🚀 SQL Roadmap 2026 — Part 10 SQL JOINs — Combining Data from Multiple Tables In real-world databases, information is rarely stored in one table. For example: customers, orders, products, payments, employees, departments A customer may exist in one table while their orders exist in another. JOINs allow us to combine related data from multiple tables. This is one of the most important SQL concepts for a Data Analyst. 🧠 1. Why Do We Need JOINs? Suppose we have two tables: customers

customer_id | customer_name
101         | Alice
102         | Bob
103         | Charlie

orders

order_id | customer_id | amount
1        | 101         | 500
2        | 101         | 800
3        | 102         | 300

The customer name is stored in "customers". The order amount is stored in "orders". To answer: «How much did each customer spend?» We need to combine the tables. That's where JOIN comes in. 🔗 2. Basic JOIN Structure

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Here: customers → c, orders → o. These are called table aliases. The condition: ON c.customer_id = o.customer_id tells SQL how the tables are related. 🔑 3. The JOIN Key A JOIN usually connects tables through a related column.

customers.customer_id ↓ orders.customer_id

Often: one table contains a primary key, another table contains the corresponding foreign key. Example: customers.customer_id → Primary Key, orders.customer_id → Foreign Key. 🧩 4. INNER JOIN "INNER JOIN" returns only rows that have a match in both tables.

SELECT c.customer_name, o.order_id, o.amount 
FROM customers c 
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result:

Alice | 1 | 500
Alice | 2 | 800
Bob   | 3 | 300

Charlie is missing because Charlie has no matching order. Customers ∩ Orders - Only matching records. 👈 5. LEFT JOIN "LEFT JOIN" returns: All rows from the left table + matching rows from the right table.

SELECT c.customer_name, o.order_id, o.amount 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result includes:

Charlie | NULL | NULL

🎯 6. Finding Customers Who Never Ordered This is a very common interview and analytics problem.

SELECT c.customer_id, c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

This technique is often called an anti-join pattern. 👉 7. RIGHT JOIN "RIGHT JOIN" returns: All rows from the right table + matching rows from the left table. In practice, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN by switching table order because it is often easier to read. 🔄 8. FULL OUTER JOIN "FULL OUTER JOIN" returns: All rows from both tables, whether they match or not. Conceptually: LEFT JOIN + RIGHT JOIN It can reveal: matching records, customers without orders, orders without matching customers. ⚠️ Not every database supports "FULL OUTER JOIN" directly. 🆚 9. INNER JOIN vs LEFT JOIN • INNER JOIN = Returns only customers with matching orders. • LEFT JOIN = Returns all customers, including those without orders. Simple rule: INNER JOIN = matching records, LEFT JOIN = keep everything from the left table. 📊 10. JOIN + Aggregation Question: «How much has each customer spent?»

🚀 𝗧𝗔𝗧𝗔 𝗚𝗿𝗼𝘂𝗽 𝗙𝗥𝗘𝗘 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀 😍 Tata Group/TCS virtual job simulations let you work through industry-style tasks and strengthen your resume. 🎓 3 FREE Virtual Programs: 📊 Data Visualisation 🔐 Cybersecurity 🌱 ESG (Environmental, Social & Governance) 💻 Virtual & flexible 🎓 Free Certificate on Completion 📄 Add the experience to your Resume/LinkedIn 🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:- https://pdlink.in/4yoXEOI 🔥 Perfect for Students • Freshers • Job Seekers