0

This is a question I got at an interview. They asked me to describe the differences in the following 3 scenarios.

Query #1:

SELECT p.name, c.email
FROM PersonalDetails as p
JOIN ContactDetails c
ON p.id = c.id
WHERE p.region = 'UK'

Query #2

SELECT p.name, c.email
FROM PersonalDetails as p
LEFT JOIN ContactDetails c
ON p.id = c.id
WHERE p.region = 'UK'

Query #3:

SELECT p.name, c.email
FROM PersonalDetails as p
LEFT JOIN ContactDetails c
ON p.id = c.id AND p.region = 'USA'

My answer- 1st is an inner join and 2nd is a LEFT JOIN.Therefore 1st query will return only matching records from both tables and 2nd query will return matching records from both tables and all records from left table.

Their answer - the first 2 queries will return the same result. The 3rd query will be executed as an inner join

Can anyone explain how this can happen...???

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SriniShine
  • 1,089
  • 5
  • 26
  • 46
  • 2
    Either you have remembered the queries incorrectly or their answers are wrong. – Martin Smith Feb 22 '13 at 16:15
  • Query #1 and #2 would return the same results if there was business logic that stated there's one set of personal details for contactdetails, that's internal business logic, not tech knowledge. The third one is an inner join because of p.id = c.id, the third actually confirms the business logic above BASED ON THEIR ANSWER. Either way, nobody respectable uses "join", it's "inner join" & changing a left join to an inner by binding the pks is an idiotic technique employed by only the most incompetent of sql developers. Feel better :) – RandomUs1r Feb 22 '13 at 17:34
  • I'm extreamly sorry.there is a mistake in the query i wrote.I wrote what I remebered.I referred to one of your threads and found the question.... http://stackoverflow.com/questions/2119859/questions-every-good-database-sql-developer-should-be-able-to-answer ..........Thanks alot everyone – SriniShine Feb 23 '13 at 14:01

2 Answers2

3

Let's break this down:

  • Query 1 is an inner join; JOIN by itself is the same as INNER JOIN. Strike 1 for the interviewer.

  • Query 2 and 3 are both outer joins. Even though there is a WHERE clause in #2, and an extra JOIN clause in #3, it is against the parent table, and won't filter anything out from the child table. Strike 2 and 3 for the interviewer.

Either they were testing you to see if you would defend your answers, they don't understand SQL, or you remember wrong.

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
0

If you are asking how the interviewer could have been so wrong, and are not asking whether you were right or not, this might be better suited to The Workplace within SE.

I was in a similar situation where the hiring manager holding the interview was not a technical person, and had consulted a technical person about types of questions to ask RE: RDBMS and other topics. As a result, the manager's grasp of the technical concepts was less than stellar.

I have also heard multiple stories of interviews where an interviewer would deliberately bait the applicant, by providing a wrong answer, or by asking the same question multiple times, simply to see how the applicant would handle the situation.

JAGAnalyst
  • 320
  • 1
  • 2
  • 10