Week 01

INNER & LEFT JOIN: Combining Tables

Day 42 to 3 hrs

What to Focus On

Joining tables is usually straightforward until one record has several matches, or no match at all. That is where a report starts counting the same person twice or quietly drops someone who should have appeared with a zero.

Before writing a join, decide what one output row represents: an order, a customer, an offer, or a total per company. Then ask which records must remain even when the other table has nothing for them.

We will work through those decisions using students and offers. The same reasoning applies to customers and orders, employees and departments, or products and sales.

Follow the Matching Rows

A join produces one result row for each pair that satisfies its ON condition. A student with two offers therefore produces two joined rows. This is the logical result; the database need not physically compare every possible pair to find it.

We will use these three tables throughout the lesson.

students

student_id	name	branch	cgpa
1	Asha	CSE	8.9
2	Bilal	ECE	7.4
3	Chitra	CSE	9.1
4	Deepak	Mech	6.8
5	Esha	CSE	8.2
6	Farhan	ECE	7.9

placements

offer_id	student_id	company_id	ctc_lpa	offer_date
101	1	10	12.0	2026-08-05
102	3	10	18.0	2026-08-05
103	3	12	22.5	2026-08-20
104	5	11	9.5	2026-08-12
105	6	12	16.0	2026-08-22

companies

company_id	company_name	city
10	Zomato	Gurugram
11	Swiggy	Bengaluru
12	Flipkart	Bengaluru
13	Zepto	Hyderabad

Note the shape of this data, because every interesting case comes out of it. Bilal and Deepak are not placed yet, so they have no row at all in placements. Chitra has two offers. Zepto visited the campus but hired nobody.

Start with the student list. These are the six records we are joining from.

SQL

SELECT s.student_id, s.name
FROM students AS s
ORDER BY s.student_id;

student_id	name
1	Asha
2	Bilal
3	Chitra
4	Deepak
5	Esha
6	Farhan

Six students in, six rows out. AS s is an alias, a nickname for the table so you can write s.name instead of students.name.

Step 2: attach the second table. JOIN ... ON is the keyword pair that does it: JOIN names the second table, ON states the condition deciding which rows belong together. A plain JOIN is an INNER JOIN, and inner means "keep only the pairs that matched".

SQL

SELECT s.name, p.offer_id, p.ctc_lpa
FROM students AS s
JOIN placements AS p ON p.student_id = s.student_id
ORDER BY s.student_id, p.offer_id;

name	offer_id	ctc_lpa
Asha	101	12.0
Chitra	102	18.0
Chitra	103	22.5
Esha	104	9.5
Farhan	105	16.0

Six students went in, five rows came out. Bilal and Deepak vanished because they have no offer to pair with, and Chitra is printed twice because she has two.

Step 3: change one word and keep everybody. If the question says "including students with no offer", the inner join is already wrong. Replace JOIN with LEFT JOIN: keep every row of the table written first, partner or no partner.

SQL

SELECT s.name, p.offer_id, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
ORDER BY s.student_id, p.offer_id;

name	offer_id	ctc_lpa
Asha	101	12.0
Bilal	NULL	NULL
Chitra	102	18.0
Chitra	103	22.5
Deepak	NULL	NULL
Esha	104	9.5
Farhan	105	16.0

There are seven rows now. Bilal and Deepak remain, with NULL in the offer columns. Chitra still appears twice: preserving unmatched students does not remove multiple matches.

So the two joins differ in exactly one behaviour: what to do with a row that found no partner.

Text

students          placements
+----------+      +--------+
| Asha     |------| 12.0   |
| Chitra   |--+---| 18.0   |
|          |  +---| 22.5   |     one student, two rows out
| Bilal    |      |        |     INNER: dropped
| Deepak   |      |        |     LEFT : kept, NULL padded
+----------+      +--------+

Take one left row
check the ON condition against every right row
match found: emit one row per match
no match: INNER drops it, LEFT keeps it with NULLs

IMP
Keep in mind: LEFT JOIN preserves every left row at the join stage. A later WHERE condition can still remove it.

The Pattern

Use these decisions to build the query:

Start FROM with the table you must not lose rows from. "Every student, placed or not" means students is written first.
Attach the next table with INNER JOIN if a missing partner should delete the row, and with LEFT JOIN if it should not.
Write the condition using the key that defines the relationship. Names need not be unique.
Alias both tables (s, p) and prefix every column with the alias.
Decide where the filter goes. Note this point: a condition on the right table goes in ON if you want the unmatched left rows kept, and in WHERE if you do not.
Aggregate if the question asks for a count or a total, then ORDER BY exactly as the statement says.

For each student with CGPA 7.0 or above, count their offers and keep those with at least one. The LEFT JOIN below makes each filtering stage visible; an inner join would give the same final result for this particular question.

SQL

SELECT s.name, COUNT(p.offer_id) AS offers
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
WHERE s.cgpa >= 7.0
GROUP BY s.student_id, s.name
HAVING COUNT(p.offer_id) >= 1
ORDER BY offers DESC, s.name ASC;

Read the query line by line.

FROM students AS s takes the students table and nicknames it s.
LEFT JOIN placements AS p ON p.student_id = s.student_id pairs each student with her offers and keeps the ones who have none, padding their offer columns with NULL.
WHERE s.cgpa >= 7.0 keeps only rows where the CGPA condition is true. WHERE runs on individual rows, before any grouping.
GROUP BY s.student_id, s.name collapses all rows of one student into a single group, so a count can be taken per student. Yesterday's keyword: one output row per group.
HAVING COUNT(p.offer_id) >= 1 throws away whole groups. It exists because WHERE cannot see a count, it runs too early.
SELECT s.name, COUNT(p.offer_id) AS offers decides what each surviving group prints.
ORDER BY offers DESC, s.name ASC sorts the rows, most offers first, alphabetically on a tie.

Dry Run:

step	what it does here	rows / groups alive
FROM + LEFT JOIN	6 students, Chitra doubled, Bilal and Deepak NULL padded	7 rows
WHERE	`cgpa >= 7.0` drops Deepak's padded row	6 rows
GROUP BY	one group per student, Chitra's two rows collapse	5 groups
HAVING	Bilal's group counts 0 offers, so it is dropped	4 groups
SELECT	`COUNT(p.offer_id)` computed per group	4 rows
ORDER BY	offers descending, then name	4 rows

name	offers
Chitra	2
Asha	1
Esha	1
Farhan	1

Chitra tops the list with two offers, and the other three tie at 1 so they come out alphabetically.

FROM + JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Count matches, not padded rows. Compare both counts after the same LEFT JOIN.

name	COUNT(*)	COUNT(p.offer_id)
Asha	1	1
Bilal	1	0
Chitra	2	2
Deepak	1	0
Esha	1	1
Farhan	1	1

The two columns agree everywhere except on Bilal and Deepak, the exact students the question cares about.

COUNT(*) counts rows, and the padded row the LEFT JOIN created for Bilal is still a row, so it reports 1 offer for a student who has none. COUNT(p.offer_id) counts only the non NULL values of that column, so it correctly reports 0.

To count matches while keeping zeroes, count a right-table column that is non-null on every real match, such as its primary key.

Check what you learned

A student with no offer must show 0. Which count do you write after the LEFT JOIN?

Query Templates

SQL

-- 1. Inner join: only students who actually have an offer, above a cutoff.
SELECT s.name, p.ctc_lpa
FROM students AS s
JOIN placements AS p ON p.student_id = s.student_id
WHERE p.ctc_lpa >= 15
ORDER BY p.ctc_lpa DESC;

-- 2. Keep every student, count offers, zeros included.
SELECT s.name, COUNT(p.offer_id) AS offers
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY offers DESC, s.name ASC;

-- 3. Keep every student, take a measure, turn the NULL into 0.
SELECT s.name, COALESCE(MAX(p.ctc_lpa), 0) AS best_ctc
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY best_ctc DESC, s.name ASC;

-- 4. Three tables. placements is the link table, so start there.
SELECT s.name, c.company_name, p.ctc_lpa
FROM placements AS p
JOIN students  AS s ON s.student_id = p.student_id
JOIN companies AS c ON c.company_id = p.company_id
WHERE c.city = 'Bengaluru'
ORDER BY p.ctc_lpa DESC;

COALESCE(x, 0) in template 3 returns the first argument that is not NULL, so it is the standard way of turning "no offer" into a printed 0.

Template 4 gives exactly this:

name	company_name	ctc_lpa
Chitra	Flipkart	22.5
Farhan	Flipkart	16.0
Esha	Swiggy	9.5

Only Bengaluru offers survive, so Zomato's two are gone, and the rows sort by package descending.

Output format. Check the required order, decimal places and treatment of missing values. On a SQLite judge that compares formatted output, these details matter even when the rows are correct.

Template 3 with PRINTF('%.2f', ...) and ORDER BY s.student_id produces this text:

Text

Asha|12.00
Bilal|0.00
Chitra|22.50
Deepak|0.00
Esha|9.50
Farhan|16.00

Without COALESCE, the raw MAX(p.ctc_lpa) is NULL for Bilal. Selecting that value directly may display an empty field. SQLite's printf('%.2f', NULL), however, produces 0.00. Keep COALESCE when zero is the intended business rule so that the calculation is explicit and does not depend on formatting behaviour. Check these three details:

Order. If the statement names an order, copy it exactly, tie breaker column included.
Decimals. PRINTF('%.2f', x) always prints two digits. ROUND(12.5, 2) prints 12.5, which is a different string and therefore a wrong answer.
NULL. Decide what an empty side should print and force it with COALESCE.

Variations

Variation 1 - Three tables and more

Chaining is not a new idea, it is the same join written twice. Each JOIN ... ON applies to everything joined so far, so the third table can attach to either of the first two.

Start from the table that links the others, usually the one holding both ids. Here that is placements, which carries student_id and company_id. In a library schema it is books_issued, in a bank schema transactions.

Build it in two steps rather than writing all three tables at once. First, offers with student names:

SQL

SELECT p.offer_id, s.name, p.company_id, p.ctc_lpa
FROM placements AS p
JOIN students AS s ON s.student_id = p.student_id
ORDER BY p.offer_id;

offer_id	name	company_id	ctc_lpa
101	Asha	10	12.0
102	Chitra	10	18.0
103	Chitra	12	22.5
104	Esha	11	9.5
105	Farhan	12	16.0

Still five rows, one per offer. The join only swapped a student_id for a readable name.

Now hop once more to companies on company_id, add the city filter, and you have template 4 exactly. Each hop is one JOIN line.

Variation 2 - LEFT JOIN across two hops

"Number of students hired by each company, including companies that hired nobody." Company to offer is one hop, offer to student is the second, and the first hop can already be empty.

SQL

SELECT c.company_name, COUNT(s.student_id) AS hires
FROM companies AS c
LEFT JOIN placements AS p ON p.company_id = c.company_id
LEFT JOIN students   AS s ON s.student_id = p.student_id
GROUP BY c.company_id, c.company_name
ORDER BY hires DESC, c.company_name ASC;

company_name	hires
Flipkart	2
Zomato	2
Swiggy	1
Zepto	0

Zepto is the row the question was really testing, and it correctly shows 0.

Change only the second LEFT JOIN to a plain JOIN and look at what you get:

company_name	hires
Flipkart	2
Zomato	2
Swiggy	1

Zepto is gone. After the first hop it is one padded row whose student_id is NULL, and an inner join at the second hop demands a real match, so that row is deleted. Three rows instead of four, and the hidden case fails.

IMP
Watch the join path: A later inner join that needs a key from the nullable side removes its unmatched rows. Keep that join LEFT too when those rows must survive. This does not mean every unrelated join in the query must be a left join.

Variation 3 - Filter in ON vs filter in WHERE

Same LEFT JOIN, same condition ctc_lpa >= 15, in two different places. The placement changes which students remain.

SQL

-- (a) condition in WHERE
SELECT s.name, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
WHERE p.ctc_lpa >= 15
ORDER BY s.student_id;

-- (b) same condition inside ON
SELECT s.name, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p
  ON p.student_id = s.student_id AND p.ctc_lpa >= 15
ORDER BY s.student_id;

Output of (a):

name	ctc_lpa
Chitra	18.0
Chitra	22.5
Farhan	16.0

Output of (b):

name	ctc_lpa
Asha	NULL
Bilal	NULL
Chitra	18.0
Chitra	22.5
Deepak	NULL
Esha	NULL
Farhan	16.0

Moving one condition changes the result from three rows to seven.

Version (a) has quietly turned into an inner join. The padded rows carry ctc_lpa as NULL, NULL >= 15 is unknown rather than true, and WHERE keeps only rows that are definitely true.

Version (b) puts the condition in ON, which runs while the pairing is decided, before any padding. Asha's 12.0 offer fails to pair, so Asha stays with a NULL package.

Which is correct depends on the wording. "Students who have an offer of 15 LPA or more" is (a). "All students, with their 15 LPA or more offer if any" is (b).

Variation 4 - RIGHT JOIN, FULL JOIN and USING

If your SQL engine does not support RIGHT JOIN or FULL OUTER JOIN, you can express the same result using left joins. Check the dialect before using either keyword. MySQL, for example, supports right joins but has no native full outer join.

SQL

-- MySQL or Postgres: A RIGHT JOIN B ON A.k = B.k
-- Same thing in SQLite, just swap the table order:
SELECT ... FROM B LEFT JOIN A ON A.k = B.k;

-- FULL OUTER JOIN, emulated:
SELECT ... FROM A LEFT JOIN B ON A.k = B.k
UNION ALL
SELECT ... FROM B LEFT JOIN A ON A.k = B.k WHERE A.k IS NULL;

USING is the other shortcut you will see online. When the key has exactly the same name on both sides you may write JOIN placements USING (student_id), and the two key columns collapse into one, so a later ORDER BY student_id no longer complains. It breaks the moment the names differ, id on one side and student_id on the other, which is most real schemas. Keep ON as your default in an OA.

Check what you learned

You wrote companies LEFT JOIN placements LEFT JOIN students and Zepto correctly showed 0 hires. You now change the second join to a plain JOIN. What happens to Zepto?

Common Mistakes

Counting padded rows as matches. COUNT(*) gives Bilal one row after the left join, even though he has no offer. COUNT(p.offer_id) gives the required zero.
Filtering away the rows you meant to retain. WHERE p.ctc_lpa >= 15 removes unmatched students. Put the cutoff in ON if every student must remain, with qualifying offers shown where available.
Confusing offers with students. Chitra contributes two offer rows. If the answer needs one row per student, group by student id or use an existence check. DISTINCT name can merge different people who share a name.
Using an ambiguous column. After joining tables that both have student_id, qualify it as s.student_id or p.student_id. This also makes the query easier to review.
Replacing NULL without checking the requirement. COALESCE(MAX(p.ctc_lpa), 0) is appropriate if no offer must appear as zero. Otherwise, retain the missing value or use the label requested by the question.
Leaving the order incomplete. If the question specifies a tie breaker, include it. The main sort column alone does not determine the order of tied rows.
Mixing calculation and formatting. In SQLite, printf('%.2f', x) gives fixed two-decimal text; ROUND does not guarantee trailing zeroes. For integer division, multiply by 1.0 before dividing when the calculation needs a fractional result.

Interview Notes

INNER JOIN or LEFT JOIN? An inner join keeps matching pairs. A left join also retains unmatched left rows, filling right-side columns with NULL. Choose based on whether records without a match belong in the answer.

Why did the row count increase? A left row may have several matches. Check the right-side key with SELECT student_id, COUNT(*) FROM placements GROUP BY student_id HAVING COUNT(*) > 1. Then confirm whether the result should represent students or offers. More rows are not automatically a bug.

When does a filter belong in ON? ON decides which pairs match; WHERE filters the joined result. A right-side cutoff in ON can retain left rows with no qualifying match. The same cutoff in WHERE removes them. A WHERE test such as p.offer_id IS NULL deliberately selects the unmatched rows.

How do you replace a RIGHT JOIN? Swap the table order and use LEFT JOIN with the same matching condition. Check the selected columns and their order after the rewrite.

How do you emulate FULL OUTER JOIN? Combine A LEFT JOIN B with the unmatched rows from B LEFT JOIN A using UNION ALL. Test a non-nullable key from A in the second branch so matched pairs are not added twice.

USING or ON? USING(col) requires the join column to have the same name in both tables and exposes one merged join column. ON works with different names and more general conditions. Use the form that makes the relationship clear.

What happens when a join key is NULL? An equality condition does not match NULL, even with another NULL. An inner join therefore excludes that row unless another part of the join condition can match it. If the requirement treats missing keys as equivalent, write that rule explicitly.

Quick Test

Check what you learned

SELECT COUNT(*) FROM students s LEFT JOIN placements p ON p.student_id = s.student_id on the tables above. What is the count?

Week 01

INNER & LEFT JOIN: Combining Tables

Day 42 to 3 hrs

What to Focus On

We will work through those decisions using students and offers. The same reasoning applies to customers and orders, employees and departments, or products and sales.

Follow the Matching Rows

We will use these three tables throughout the lesson.

students

student_id	name	branch	cgpa
1	Asha	CSE	8.9
2	Bilal	ECE	7.4
3	Chitra	CSE	9.1
4	Deepak	Mech	6.8
5	Esha	CSE	8.2
6	Farhan	ECE	7.9

placements

offer_id	student_id	company_id	ctc_lpa	offer_date
101	1	10	12.0	2026-08-05
102	3	10	18.0	2026-08-05
103	3	12	22.5	2026-08-20
104	5	11	9.5	2026-08-12
105	6	12	16.0	2026-08-22

companies

company_id	company_name	city
10	Zomato	Gurugram
11	Swiggy	Bengaluru
12	Flipkart	Bengaluru
13	Zepto	Hyderabad

Start with the student list. These are the six records we are joining from.

SQL

SELECT s.student_id, s.name
FROM students AS s
ORDER BY s.student_id;

student_id	name
1	Asha
2	Bilal
3	Chitra
4	Deepak
5	Esha
6	Farhan

Six students in, six rows out. AS s is an alias, a nickname for the table so you can write s.name instead of students.name.

SQL

SELECT s.name, p.offer_id, p.ctc_lpa
FROM students AS s
JOIN placements AS p ON p.student_id = s.student_id
ORDER BY s.student_id, p.offer_id;

name	offer_id	ctc_lpa
Asha	101	12.0
Chitra	102	18.0
Chitra	103	22.5
Esha	104	9.5
Farhan	105	16.0

Six students went in, five rows came out. Bilal and Deepak vanished because they have no offer to pair with, and Chitra is printed twice because she has two.

SQL

SELECT s.name, p.offer_id, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
ORDER BY s.student_id, p.offer_id;

name	offer_id	ctc_lpa
Asha	101	12.0
Bilal	NULL	NULL
Chitra	102	18.0
Chitra	103	22.5
Deepak	NULL	NULL
Esha	104	9.5
Farhan	105	16.0

There are seven rows now. Bilal and Deepak remain, with NULL in the offer columns. Chitra still appears twice: preserving unmatched students does not remove multiple matches.

So the two joins differ in exactly one behaviour: what to do with a row that found no partner.

Text

students          placements
+----------+      +--------+
| Asha     |------| 12.0   |
| Chitra   |--+---| 18.0   |
|          |  +---| 22.5   |     one student, two rows out
| Bilal    |      |        |     INNER: dropped
| Deepak   |      |        |     LEFT : kept, NULL padded
+----------+      +--------+

Take one left row
check the ON condition against every right row
match found: emit one row per match
no match: INNER drops it, LEFT keeps it with NULLs

IMP
Keep in mind: LEFT JOIN preserves every left row at the join stage. A later WHERE condition can still remove it.

The Pattern

Use these decisions to build the query:

Start FROM with the table you must not lose rows from. "Every student, placed or not" means students is written first.
Attach the next table with INNER JOIN if a missing partner should delete the row, and with LEFT JOIN if it should not.
Write the condition using the key that defines the relationship. Names need not be unique.
Alias both tables (s, p) and prefix every column with the alias.
Decide where the filter goes. Note this point: a condition on the right table goes in ON if you want the unmatched left rows kept, and in WHERE if you do not.
Aggregate if the question asks for a count or a total, then ORDER BY exactly as the statement says.

SQL

SELECT s.name, COUNT(p.offer_id) AS offers
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
WHERE s.cgpa >= 7.0
GROUP BY s.student_id, s.name
HAVING COUNT(p.offer_id) >= 1
ORDER BY offers DESC, s.name ASC;

Read the query line by line.

FROM students AS s takes the students table and nicknames it s.
LEFT JOIN placements AS p ON p.student_id = s.student_id pairs each student with her offers and keeps the ones who have none, padding their offer columns with NULL.
WHERE s.cgpa >= 7.0 keeps only rows where the CGPA condition is true. WHERE runs on individual rows, before any grouping.
GROUP BY s.student_id, s.name collapses all rows of one student into a single group, so a count can be taken per student. Yesterday's keyword: one output row per group.
HAVING COUNT(p.offer_id) >= 1 throws away whole groups. It exists because WHERE cannot see a count, it runs too early.
SELECT s.name, COUNT(p.offer_id) AS offers decides what each surviving group prints.
ORDER BY offers DESC, s.name ASC sorts the rows, most offers first, alphabetically on a tie.

Dry Run:

step	what it does here	rows / groups alive
FROM + LEFT JOIN	6 students, Chitra doubled, Bilal and Deepak NULL padded	7 rows
WHERE	`cgpa >= 7.0` drops Deepak's padded row	6 rows
GROUP BY	one group per student, Chitra's two rows collapse	5 groups
HAVING	Bilal's group counts 0 offers, so it is dropped	4 groups
SELECT	`COUNT(p.offer_id)` computed per group	4 rows
ORDER BY	offers descending, then name	4 rows

name	offers
Chitra	2
Asha	1
Esha	1
Farhan	1

Chitra tops the list with two offers, and the other three tie at 1 so they come out alphabetically.

FROM + JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Count matches, not padded rows. Compare both counts after the same LEFT JOIN.

name	COUNT(*)	COUNT(p.offer_id)
Asha	1	1
Bilal	1	0
Chitra	2	2
Deepak	1	0
Esha	1	1
Farhan	1	1

The two columns agree everywhere except on Bilal and Deepak, the exact students the question cares about.

To count matches while keeping zeroes, count a right-table column that is non-null on every real match, such as its primary key.

Check what you learned

A student with no offer must show 0. Which count do you write after the LEFT JOIN?

Query Templates

SQL

-- 1. Inner join: only students who actually have an offer, above a cutoff.
SELECT s.name, p.ctc_lpa
FROM students AS s
JOIN placements AS p ON p.student_id = s.student_id
WHERE p.ctc_lpa >= 15
ORDER BY p.ctc_lpa DESC;

-- 2. Keep every student, count offers, zeros included.
SELECT s.name, COUNT(p.offer_id) AS offers
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY offers DESC, s.name ASC;

-- 3. Keep every student, take a measure, turn the NULL into 0.
SELECT s.name, COALESCE(MAX(p.ctc_lpa), 0) AS best_ctc
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY best_ctc DESC, s.name ASC;

-- 4. Three tables. placements is the link table, so start there.
SELECT s.name, c.company_name, p.ctc_lpa
FROM placements AS p
JOIN students  AS s ON s.student_id = p.student_id
JOIN companies AS c ON c.company_id = p.company_id
WHERE c.city = 'Bengaluru'
ORDER BY p.ctc_lpa DESC;

COALESCE(x, 0) in template 3 returns the first argument that is not NULL, so it is the standard way of turning "no offer" into a printed 0.

Template 4 gives exactly this:

name	company_name	ctc_lpa
Chitra	Flipkart	22.5
Farhan	Flipkart	16.0
Esha	Swiggy	9.5

Only Bengaluru offers survive, so Zomato's two are gone, and the rows sort by package descending.

Output format. Check the required order, decimal places and treatment of missing values. On a SQLite judge that compares formatted output, these details matter even when the rows are correct.

Template 3 with PRINTF('%.2f', ...) and ORDER BY s.student_id produces this text:

Text

Asha|12.00
Bilal|0.00
Chitra|22.50
Deepak|0.00
Esha|9.50
Farhan|16.00

Order. If the statement names an order, copy it exactly, tie breaker column included.
Decimals. PRINTF('%.2f', x) always prints two digits. ROUND(12.5, 2) prints 12.5, which is a different string and therefore a wrong answer.
NULL. Decide what an empty side should print and force it with COALESCE.

Variations

Variation 1 - Three tables and more

Chaining is not a new idea, it is the same join written twice. Each JOIN ... ON applies to everything joined so far, so the third table can attach to either of the first two.

Build it in two steps rather than writing all three tables at once. First, offers with student names:

SQL

SELECT p.offer_id, s.name, p.company_id, p.ctc_lpa
FROM placements AS p
JOIN students AS s ON s.student_id = p.student_id
ORDER BY p.offer_id;

offer_id	name	company_id	ctc_lpa
101	Asha	10	12.0
102	Chitra	10	18.0
103	Chitra	12	22.5
104	Esha	11	9.5
105	Farhan	12	16.0

Still five rows, one per offer. The join only swapped a student_id for a readable name.

Now hop once more to companies on company_id, add the city filter, and you have template 4 exactly. Each hop is one JOIN line.

Variation 2 - LEFT JOIN across two hops

"Number of students hired by each company, including companies that hired nobody." Company to offer is one hop, offer to student is the second, and the first hop can already be empty.

SQL

SELECT c.company_name, COUNT(s.student_id) AS hires
FROM companies AS c
LEFT JOIN placements AS p ON p.company_id = c.company_id
LEFT JOIN students   AS s ON s.student_id = p.student_id
GROUP BY c.company_id, c.company_name
ORDER BY hires DESC, c.company_name ASC;

company_name	hires
Flipkart	2
Zomato	2
Swiggy	1
Zepto	0

Zepto is the row the question was really testing, and it correctly shows 0.

Change only the second LEFT JOIN to a plain JOIN and look at what you get:

company_name	hires
Flipkart	2
Zomato	2
Swiggy	1

IMP
Watch the join path: A later inner join that needs a key from the nullable side removes its unmatched rows. Keep that join LEFT too when those rows must survive. This does not mean every unrelated join in the query must be a left join.

Variation 3 - Filter in ON vs filter in WHERE

Same LEFT JOIN, same condition ctc_lpa >= 15, in two different places. The placement changes which students remain.

SQL

-- (a) condition in WHERE
SELECT s.name, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p ON p.student_id = s.student_id
WHERE p.ctc_lpa >= 15
ORDER BY s.student_id;

-- (b) same condition inside ON
SELECT s.name, p.ctc_lpa
FROM students AS s
LEFT JOIN placements AS p
  ON p.student_id = s.student_id AND p.ctc_lpa >= 15
ORDER BY s.student_id;

Output of (a):

name	ctc_lpa
Chitra	18.0
Chitra	22.5
Farhan	16.0

Output of (b):

name	ctc_lpa
Asha	NULL
Bilal	NULL
Chitra	18.0
Chitra	22.5
Deepak	NULL
Esha	NULL
Farhan	16.0

Moving one condition changes the result from three rows to seven.

Version (a) has quietly turned into an inner join. The padded rows carry ctc_lpa as NULL, NULL >= 15 is unknown rather than true, and WHERE keeps only rows that are definitely true.

Version (b) puts the condition in ON, which runs while the pairing is decided, before any padding. Asha's 12.0 offer fails to pair, so Asha stays with a NULL package.

Which is correct depends on the wording. "Students who have an offer of 15 LPA or more" is (a). "All students, with their 15 LPA or more offer if any" is (b).

Variation 4 - RIGHT JOIN, FULL JOIN and USING

SQL

-- MySQL or Postgres: A RIGHT JOIN B ON A.k = B.k
-- Same thing in SQLite, just swap the table order:
SELECT ... FROM B LEFT JOIN A ON A.k = B.k;

-- FULL OUTER JOIN, emulated:
SELECT ... FROM A LEFT JOIN B ON A.k = B.k
UNION ALL
SELECT ... FROM B LEFT JOIN A ON A.k = B.k WHERE A.k IS NULL;

Check what you learned

You wrote companies LEFT JOIN placements LEFT JOIN students and Zepto correctly showed 0 hires. You now change the second join to a plain JOIN. What happens to Zepto?

Common Mistakes

Counting padded rows as matches. COUNT(*) gives Bilal one row after the left join, even though he has no offer. COUNT(p.offer_id) gives the required zero.
Filtering away the rows you meant to retain. WHERE p.ctc_lpa >= 15 removes unmatched students. Put the cutoff in ON if every student must remain, with qualifying offers shown where available.
Confusing offers with students. Chitra contributes two offer rows. If the answer needs one row per student, group by student id or use an existence check. DISTINCT name can merge different people who share a name.
Using an ambiguous column. After joining tables that both have student_id, qualify it as s.student_id or p.student_id. This also makes the query easier to review.
Replacing NULL without checking the requirement. COALESCE(MAX(p.ctc_lpa), 0) is appropriate if no offer must appear as zero. Otherwise, retain the missing value or use the label requested by the question.
Leaving the order incomplete. If the question specifies a tie breaker, include it. The main sort column alone does not determine the order of tied rows.
Mixing calculation and formatting. In SQLite, printf('%.2f', x) gives fixed two-decimal text; ROUND does not guarantee trailing zeroes. For integer division, multiply by 1.0 before dividing when the calculation needs a fractional result.

Interview Notes

How do you replace a RIGHT JOIN? Swap the table order and use LEFT JOIN with the same matching condition. Check the selected columns and their order after the rewrite.

Quick Test

Check what you learned

SELECT COUNT(*) FROM students s LEFT JOIN placements p ON p.student_id = s.student_id on the tables above. What is the count?