DEV Community

Er. Bhupendra
Er. Bhupendra

Posted on • Edited on

MYSQL 2nd ON JOINS PRACTICES

Yes. The best way to learn JOINs is to use one database for all questions.

We'll use 4 tables.

Table 1: Department 

CREATE TABLE Department (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT
);

INSERT INTO Department VALUES
(1,'IT'),
(2,'HR'),
(3,'Finance'),
(4,'Sales');
Enter fullscreen mode Exit fullscreen mode

Table 2: Employee 

CREATE TABLE Employee (
    emp_id INTEGER PRIMARY KEY,
    emp_name TEXT,
    salary INTEGER,
    dept_id INTEGER,
    manager_id INTEGER
);

INSERT INTO Employee VALUES
(1,'Rahul',70000,1,5),
(2,'Amit',60000,1,5),
(3,'Neha',50000,2,6),
(4,'Priya',65000,3,6),
(5,'Rohit',90000,1,NULL),
(6,'Ankit',95000,2,NULL),
(7,'Karan',55000,NULL,NULL);
Enter fullscreen mode Exit fullscreen mode

Table 3: Project 

CREATE TABLE Project (
    project_id INTEGER PRIMARY KEY,
    project_name TEXT
);

INSERT INTO Project VALUES
(101,'Banking'),
(102,'Ecommerce'),
(103,'Healthcare'),
(104,'AI');
Enter fullscreen mode Exit fullscreen mode

Table 4: Employee_Project 

CREATE TABLE Employee_Project (
    emp_id INTEGER,
    project_id INTEGER
);

INSERT INTO Employee_Project VALUES
(1,101),
(1,102),
(2,102),
(3,103),
(4,101),
(5,104);
Enter fullscreen mode Exit fullscreen mode

JOIN Practice Questions

INNER JOIN

Q1

Employee Name + Department Name

Q2

Employee Salary + Department Name

Q3

Employees working in IT

Q4

Employees working in HR

Q5

Department-wise Employee List

Q6

Employee + Department + Salary

Q7

Employees with Department IDs

Q8

Department Names with Employees

Q9

Employee Count per Department

Q10

Highest Salary in each Department

LEFT JOIN

Q11

Show all employees with department.

Q12

Find employees without department.

Q13

Show all departments.

Q14

Departments having no employees.

Q15

Employees even if department missing.

Q16

Projects with employees.

Q17

Projects without employees.

Q18

Employees without projects.

Q19

All projects.

Q20

Departments with employee count.

RIGHT JOIN (SQLite doesn't support RIGHT JOIN)

Practice by reversing table order with LEFT JOIN.

Q21

All departments with employees.

Q22

Departments without employees.

Q23

Projects without employees.

Q24

Employees with NULL department.

Q25

Employees with department names.

SELF JOIN

Q26

Employee + Manager Name

Q27

Managers with Employees

Q28

Employees without managers

Q29

Manager-wise Employee Count

Q30

Employees reporting to Rohit

Q31

Employees reporting to Ankit

Q32

CEO (Manager NULL)

Q33

Manager hierarchy

Q34

Manager Salary + Employee Salary

Q35

Employees earning more than manager

MANY TO MANY JOIN

Q36

Employee + Project

Q37

Project + Employee Count

Q38

Project-wise Employee List

Q39

Employees in Banking Project

Q40

Employees in AI Project

Q41

Employees in more than one project

Q42

Projects having multiple employees

Q43

Projects having only one employee

Q44

Employee + Department + Project

Q45

Project + Department

Multiple JOIN

Q46

Employee + Department + Project

Q47

Department + Project Count

Q48

Highest Salary in each Project

Q49

Department-wise Project Count

Q50

Employee + Manager + Department

JOIN + GROUP BY

Q51

Department-wise Avg Salary

Q52

Department-wise Max Salary

Q53

Department-wise Min Salary

Q54

Department-wise Employee Count

Q55

Project-wise Employee Count

Q56

Project-wise Average Salary

Q57

Manager-wise Team Count

Q58

Manager-wise Average Salary

Q59

Project-wise Highest Salary

Q60

Department-wise Total Salary

JOIN + HAVING

Q61

Departments having more than 2 employees

Q62

Projects having more than 1 employee

Q63

Managers having more than 1 employee

Q64

Departments with Avg Salary >70000

Q65

Projects with Avg Salary >60000

Advanced JOIN

Q66

Second Highest Salary in each Department

Q67

Highest Paid Employee in each Project

Q68

Employees working on multiple projects

Q69

Departments with highest payroll

Q70

Top 2 salary employees in every department

Product Company Level

Q71

Employee working on all projects

Q72

Projects having all IT employees

Q73

Employees not assigned any project

Q74

Projects without employees

Q75

Departments without projects

Q76

Employees with maximum projects

Q77

Projects with maximum employees

Q78

Department having maximum employees

Q79

Manager handling maximum employees

Q80

Highest salary manager

Interview Level

Q81–Q100

  • Employees whose salary > department average
  • Employees working with their manager in same department
  • Employees sharing same project
  • Employees not sharing any project
  • Department-wise top salary
  • Department-wise bottom salary
  • Project-wise top performer
  • Manager hierarchy (2 levels)
  • Employees working in Banking & AI both
  • Employees working in Banking but not AI
  • Departments with no active projects
  • Employees on exactly one project
  • Employees on three or more projects
  • Projects with no IT employee
  • Department with highest average salary
  • Employee with maximum colleagues
  • Employees who report indirectly to a manager
  • Cross-team project participation
  • Employees without a manager but with projects
  • Complete employee–department–manager–project report

Recommended Practice Order

  1. Q1–10 → Master INNER JOIN.
  2. Q11–25 → Learn LEFT JOIN (SQLite doesn't support RIGHT JOIN, so use reversed LEFT JOIN).
  3. Q26–35 → SELF JOIN.
  4. Q36–50 → Many-to-many and multi-table joins.
  5. Q51–65 → Aggregation with joins.
  6. Q66–100 → Advanced interview-style join problems.

This progression mirrors how JOIN concepts typically build in real interviews and helps you strengthen one pattern before moving to the next.

================================

SQL JOINs — Interview Master Sheet (Analysis + Extra Questions)

Pehle seedha baat: tera existing 100-question bank solid foundation hai — INNER, LEFT, self-join, many-to-many, GROUP BY+HAVING sab cover ho gaya. Lekin agar interview mein "SQL heavy" role hai (backend/data), toh 5 gaps hain jo interviewers specifically poochte hain aur tere sheet mein missing hain. Neeche pehle gap analysis, phir un gaps ko cover karne wale naye questions + solutions.

Gap Analysis — Kya Missing Hai

Think of JOIN types as transport modes: INNER JOIN is a train jo sirf common stations pe milti hai; LEFT JOIN ek bus jo apne saare passengers ko le jaati hai chahe doosri bus khali ho; CROSS JOIN har passenger ko har seat se milaana (no filter) — yeh teesra mode tere sheet mein hai hi nahi.

Gap Kyun matter karta hai interview mein
CROSS JOIN "Cartesian product samjhao" — bahut common warm-up question, aur accidental cross join (missing ON clause) real bugs ka top cause hai
FULL OUTER JOIN emulation MySQL/SQLite mein FULL JOIN nahi hota — LEFT JOIN UNION RIGHT JOIN se banana interviewer test karta hai
Window functions with JOIN Q66, Q70 ("2nd highest", "top 2 per department") — yeh classic subquery se solve karne se puchte hain, but RANK()/DENSE_RANK() production-grade answer hai. Interviewer dono expect karta hai
ON vs WHERE placement (LEFT JOIN) Ek chhota sa trap: LEFT JOIN ... WHERE b.col = X silently INNER JOIN ban jaata hai. Bahut common "spot the bug" question
NATURAL JOIN / USING clause Kam asked but "difference from ON clause" is a quick 1-mark question
Self-join for duplicates/gaps Managers-of-managers (2-level hierarchy), duplicate row detection — self join ka ek deeper use-case jo tera Q26-35 sirf 1-level tak covers

Same 4 tables (Department, Employee, Project, Employee_Project) use karte hain — koi naya schema nahi chahiye.

Extra Questions — CROSS JOIN

Q101. Har employee ko har project ke saath match karo (Cartesian product) — dekho total kitne rows aate hain.

SELECT e.emp_name, p.project_name
FROM Employee e CROSS JOIN Project p;
-- 7 employees x 4 projects = 28 rows
Enter fullscreen mode Exit fullscreen mode

Q102. Explain the bug: SELECT * FROM Employee e, Department d; (comma join without WHERE) — yeh implicit CROSS JOIN hai. Fix karo.

-- BUG version:
SELECT * FROM Employee e, Department d;   -- 7 x 4 = 28 rows, galat!

-- FIX:
SELECT * FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;
Enter fullscreen mode Exit fullscreen mode

Extra Questions — FULL OUTER JOIN (emulated)

Q103. Saare employees AUR saare departments dikhao, chahe match ho ya na ho (FULL OUTER JOIN, SQLite/MySQL mein nahi hota).

SELECT e.emp_name, d.dept_name
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM Employee e RIGHT JOIN Department d ON e.dept_id = d.dept_id;

-- SQLite mein RIGHT bhi nahi hota, toh:
SELECT e.emp_name, d.dept_name
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM Department d LEFT JOIN Employee e ON e.dept_id = d.dept_id;
Enter fullscreen mode Exit fullscreen mode

Q104. Sirf woh rows dikhao jo dono taraf se unmatched hain (department bina employee, employee bina department) — "FULL OUTER JOIN minus INNER JOIN" pattern.

SELECT e.emp_name, d.dept_name
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL
UNION
SELECT e.emp_name, d.dept_name
FROM Department d LEFT JOIN Employee e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
Enter fullscreen mode Exit fullscreen mode

Extra Questions — Window Functions + JOIN (production-grade Q66/Q70)

Q105. Har department ka 2nd highest salary — window function se (Q66 ka better answer).

SELECT dept_name, emp_name, salary FROM (
  SELECT d.dept_name, e.emp_name, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
  FROM Employee e JOIN Department d ON e.dept_id = d.dept_id
) ranked
WHERE rnk = 2;
Enter fullscreen mode Exit fullscreen mode

Q106. Har department ke top 2 salary earners (Q70 ka window-function version).

SELECT dept_name, emp_name, salary FROM (
  SELECT d.dept_name, e.emp_name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
  FROM Employee e JOIN Department d ON e.dept_id = d.dept_id
) ranked
WHERE rn <= 2;
Enter fullscreen mode Exit fullscreen mode

Q107. Har project ka highest-paid employee (Q67 ka window version — ties bhi handle karta hai).

SELECT project_name, emp_name, salary FROM (
  SELECT p.project_name, e.emp_name, e.salary,
         RANK() OVER (PARTITION BY p.project_id ORDER BY e.salary DESC) AS rnk
  FROM Employee_Project ep
  JOIN Employee e ON ep.emp_id = e.emp_id
  JOIN Project p ON ep.project_id = p.project_id
) ranked
WHERE rnk = 1;
Enter fullscreen mode Exit fullscreen mode

Extra Questions — ON vs WHERE Trap (spot-the-bug, high-frequency)

Q108. Yeh query LEFT JOIN hone ke bawajood INNER JOIN jaisa result kyun deti hai? Fix karo.

-- WRONG: filters out unmatched (department-less) employees
SELECT e.emp_name, d.dept_name
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'IT';   -- yeh NULLs ko drop kar deta hai, LEFT JOIN ka fayda khatam

-- CORRECT: filter condition ON clause mein le jao
SELECT e.emp_name, d.dept_name
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id AND d.dept_name = 'IT';
Enter fullscreen mode Exit fullscreen mode

Interview one-liner: WHERE clause LEFT JOIN ke baad apply hoti hai, toh agar tu right-table column pe WHERE lagata hai, NULL rows automatically drop ho jaati hain — LEFT JOIN silently INNER JOIN ban jaata hai. Filter condition ko ON clause mein daalo agar unmatched rows preserve karni hain.

Extra Questions — Deeper Self-Join

Q109. 2-level manager hierarchy — employee, uska manager, aur manager ka manager (agar ho).

SELECT e.emp_name AS employee, m1.emp_name AS manager, m2.emp_name AS skip_level_manager
FROM Employee e
LEFT JOIN Employee m1 ON e.manager_id = m1.emp_id
LEFT JOIN Employee m2 ON m1.manager_id = m2.emp_id;
Enter fullscreen mode Exit fullscreen mode

Q110. Employees jo khud manager bhi hain aur kisi ke under bhi kaam karte hain (dono role).

SELECT DISTINCT e.emp_name
FROM Employee e
JOIN Employee sub ON sub.manager_id = e.emp_id
WHERE e.manager_id IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Q111. Ek manager jiske under 2+ employees hain, unka naam aur team size.

SELECT m.emp_name AS manager, COUNT(e.emp_id) AS team_size
FROM Employee e JOIN Employee m ON e.manager_id = m.emp_id
GROUP BY m.emp_id
HAVING COUNT(e.emp_id) >= 2;
Enter fullscreen mode Exit fullscreen mode

Extra Questions — Real Interview Curveballs

Q112. Employees jo Banking (101) AUR AI (104) dono projects mein hain (intersection via self-join on Employee_Project).

SELECT e.emp_name
FROM Employee e
JOIN Employee_Project ep1 ON e.emp_id = ep1.emp_id AND ep1.project_id = 101
JOIN Employee_Project ep2 ON e.emp_id = ep2.emp_id AND ep2.project_id = 104;
Enter fullscreen mode Exit fullscreen mode

Q113. Employees jo Banking mein hain but AI mein NAHI (set difference via LEFT JOIN + IS NULL).

SELECT e.emp_name
FROM Employee e
JOIN Employee_Project ep1 ON e.emp_id = ep1.emp_id AND ep1.project_id = 101
LEFT JOIN Employee_Project ep2 ON e.emp_id = ep2.emp_id AND ep2.project_id = 104
WHERE ep2.project_id IS NULL;
Enter fullscreen mode Exit fullscreen mode

Q114. Do employees jo same project share karte hain — pair list (self-join on Employee_Project, avoid duplicate pairs).

SELECT e1.emp_name AS emp1, e2.emp_name AS emp2, p.project_name
FROM Employee_Project ep1
JOIN Employee_Project ep2 ON ep1.project_id = ep2.project_id AND ep1.emp_id < ep2.emp_id
JOIN Employee e1 ON ep1.emp_id = e1.emp_id
JOIN Employee e2 ON ep2.emp_id = e2.emp_id
JOIN Project p ON ep1.project_id = p.project_id;
Enter fullscreen mode Exit fullscreen mode

Q115. Departments jahan koi bhi employee company-average se zyada kama raha hai.

SELECT DISTINCT d.dept_name
FROM Employee e JOIN Department d ON e.dept_id = d.dept_id
WHERE e.salary > (SELECT AVG(salary) FROM Employee);
Enter fullscreen mode Exit fullscreen mode

Q116. Employee ka salary uske department ke average se compare karo (self-referencing via JOIN on subquery).

SELECT e.emp_name, e.salary, dept_avg.avg_sal,
       CASE WHEN e.salary > dept_avg.avg_sal THEN 'Above Avg' ELSE 'Below/Equal' END AS status
FROM Employee e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM Employee GROUP BY dept_id) dept_avg
  ON e.dept_id = dept_avg.dept_id;
Enter fullscreen mode Exit fullscreen mode

Q117. Duplicate rows detect karo agar Employee_Project mein same (emp_id, project_id) pair 2 baar insert ho gaya ho (self-join method).

SELECT ep1.emp_id, ep1.project_id, COUNT(*) AS cnt
FROM Employee_Project ep1
GROUP BY ep1.emp_id, ep1.project_id
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

Quick Interview One-Liners (rapid-fire, likely to be asked verbally)

  • INNER vs LEFT vs CROSS in one line: INNER = sirf matches, LEFT = left table full + matches, CROSS = har row ka har row se combination (no condition).
  • Why is LEFT JOIN + WHERE on right table dangerous? WHERE filters after join, silently drops NULLs, join effectively INNER ban jaata hai.
  • JOIN vs Subquery — kab kya use karo? JOIN jab tumhe dono tables ka data ek saath chahiye (columns from both); subquery jab sirf filter/existence check karna hai aur columns sirf ek table se chahiye — subquery aksar zyada readable hota hai for EXISTS/NOT EXISTS checks.
  • Index impact: JOIN columns (foreign keys) pe index na ho toh optimizer nested-loop join karega jo bade tables pe slow hota hai — dept_id, manager_id, emp_id pe index expect karo.
  • USING vs ON: USING(col) sirf tab chalega jab dono tables mein column ka naam same ho aur ek hi column output mein aaye; ON flexible hai, kisi bhi condition ke liye.

Suggested Order to Add These

  1. Do Q101-102 (CROSS JOIN) right after your existing Q11-25 (LEFT JOIN block) — logical flow hai.
  2. Q103-104 (FULL OUTER emulation) fits after Q21-25 (your "RIGHT JOIN via LEFT JOIN" section).
  3. Q108 (ON vs WHERE trap) — do this early, it's a favorite 2-minute interview trap question.
  4. Q105-107 (window functions) go after your Q66-70 as the "better answer" version.
  5. Q109-117 (curveballs) as final round before mock interview.

Top comments (0)