Concept questionEasy~5 minQuerying / 1 of 7

Interview question

In an API query, when would you use an inner join versus a left join?

Explain inner join versus left join for API results

Explains join behavior using API-shaped data, missing related rows, and result-cardinality trade-offs.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I would use an inner join when the related row is required for the result to make sense. For example, listing paid invoices with their customer can use an inner join if every invoice must have a customer. I would use a left join when the main row still matters even if the related row is missing, such as listing users with an optional last login record. The choice changes which rows appear, not only which columns appear.

Full answer below

Practice focus

Join semantics, missing related data, cardinality, API projection, and practical query reasoning.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Join questions show whether a candidate can reason about result sets instead of memorizing syntax. They also reveal whether API behavior stays intentional when related data is optional.
I would use an inner join when the related row is required for the result to make sense. For example, listing paid invoices with their customer can use an inner join if every invoice must have a customer. I would use a left join when the main row still matters even if the related row is missing, such as listing users with an optional last login record. The choice changes which rows appear, not only which columns appear.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I would start from the business meaning of missing data. An inner join filters out parent rows without a match, which can hide records unexpectedly if the relationship is optional or data is incomplete. A left join keeps the parent row and returns nulls for the related columns, so the API can show a default value or an empty nested object. I would also watch one-to-many joins because they can multiply rows and force aggregation, grouping, or separate queries for a clean response shape.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In a production API I would make join choices visible in tests and monitoring because a small join change can alter counts, pagination, and customer-visible lists. If optional data is important, I would model that explicitly in the DTO rather than letting nulls leak everywhere. For complex reads, I may use a read model or two-step query so the SQL stays understandable and the API does not accidentally drop records through an inner join. SQL semantics need to line up with the product behavior users see.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Choosing join type by habit rather than by required or optional data.
Forgetting that inner joins remove parent rows with no match.
Returning duplicated parent rows from a one-to-many join.
Letting null related data break API serialization or display.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain which rows disappear with an inner join.
Treats left join as only a way to fetch extra columns.
Ignores one-to-many row multiplication.
Has no way to connect SQL results to API behavior.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would you avoid duplicate parent rows from a one-to-many join?
What would the API return when optional related data is missing?
How would this affect pagination?

Continue the section

Next in Querying: GROUP BY and HAVING

Step 2 of 7

Use GROUP BY and HAVING for business counts

Opening drill

Concept questionEasy~5 minQuerying / 1 of 7

Interview question

In an API query, when would you use an inner join versus a left join?

Explain inner join versus left join for API results

Explains join behavior using API-shaped data, missing related rows, and result-cardinality trade-offs.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

Full answer below

Practice focus

Join semantics, missing related data, cardinality, API projection, and practical query reasoning.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Join questions show whether a candidate can reason about result sets instead of memorizing syntax. They also reveal whether API behavior stays intentional when related data is optional.
I would use an inner join when the related row is required for the result to make sense. For example, listing paid invoices with their customer can use an inner join if every invoice must have a customer. I would use a left join when the main row still matters even if the related row is missing, such as listing users with an optional last login record. The choice changes which rows appear, not only which columns appear.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I would start from the business meaning of missing data. An inner join filters out parent rows without a match, which can hide records unexpectedly if the relationship is optional or data is incomplete. A left join keeps the parent row and returns nulls for the related columns, so the API can show a default value or an empty nested object. I would also watch one-to-many joins because they can multiply rows and force aggregation, grouping, or separate queries for a clean response shape.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In a production API I would make join choices visible in tests and monitoring because a small join change can alter counts, pagination, and customer-visible lists. If optional data is important, I would model that explicitly in the DTO rather than letting nulls leak everywhere. For complex reads, I may use a read model or two-step query so the SQL stays understandable and the API does not accidentally drop records through an inner join. SQL semantics need to line up with the product behavior users see.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Choosing join type by habit rather than by required or optional data.
Forgetting that inner joins remove parent rows with no match.
Returning duplicated parent rows from a one-to-many join.
Letting null related data break API serialization or display.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain which rows disappear with an inner join.
Treats left join as only a way to fetch extra columns.
Ignores one-to-many row multiplication.
Has no way to connect SQL results to API behavior.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would you avoid duplicate parent rows from a one-to-many join?
What would the API return when optional related data is missing?
How would this affect pagination?

Continue the section

Next in Querying: GROUP BY and HAVING

Step 2 of 7

Use GROUP BY and HAVING for business counts

In an API query, when would you use an inner join versus a left join?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue the section

In an API query, when would you use an inner join versus a left join?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue the section