What is the difference between Left Outer join and Left Outer join restricted- Interview question

Question

What is the difference between Left Outer join and Left Outer join restricted- Interview question

OceanDrop

SSC-Addicted

Points: 487
More actions
April 8, 2016 at 11:42 am

#309780

In my recent interview, they asked me this question: "What is the difference between Left Outer join and Left Outer join restricted?"
I never came across the term "left outer join Restricted" and I tried to google but did not find anything helpful! Could some one please explain to me ?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It depends on the interpretation. A restricted outer join, could be the simple use of a WHERE clause (which shouldn't change the outer join into an inner join) or additional criteria on the join clause that is not part of the join. E.g.

SELECT *

FROM TableA a

LEFT JOIN TableB b ON a.SomeId = b.SomeId

AND b.SomeValue = 1 --Second restriction mentioned

WHERE a.OtherValue > 200 --First restriction mentioned

I believe that these aren't official terms and they shouldn't be treated as such. However, you should be aware of where to put the conditions when using outer joins to give the correct results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

OceanDrop SSC-Addicted Points: 487 More actions · Answer 2

I see, okay! I know the logic interpretation changes how and what you use in On clause vs where clause when Outer join is in place. I guess I do not know that it is called "restricted outer join".

Makes sense! Thanks for the explanation.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

This:

I believe that these aren't official terms and they shouldn't be treated as such.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

That's the kind of interview question that would make me wonder whether the interviewer knows what they're talking about or not. Technically any query with any kind of filtering criteria is "restricted".

ScottPletcher SSC Guru Points: 101089 More actions · Answer 5

Yep, exactly. It sounds like some type of certification q too, where because MS mentions "restricted" in BOL or some other help, you're supposed to remember it exactly and regurgitate it like a robot. Of course real DBAs don't work that way anyway :).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eric M Russell SSC Guru Points: 125560 More actions · Answer 6

Here is an example of an 'unrestricted' left outer join.

select *

from TableA

left outer join TableB on 1 = 1;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125560 More actions · Answer 7

They're asking the wrong quesions.

Here are examples of questions that matter:

- How can you confirm that a query is using an index?

- What is the purpose of FILL FACTOR when creating an index?

- What is the difference between READ COMMITTED and READ UNCOMMITTED isolation level?

- Explain how LOCKING, BLOCKING, and DEADLOCKING are related.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ScottPletcher SSC Guru Points: 101089 More actions · Answer 8

Or, since we're dealing with JOINs:

What is the difference between INNER JOIN and LEFT OUTER JOIN?

What is the difference between LEFT OUTER JOIN and RIGHT OUTER JOIN?

What is a FULL OUTER JOIN?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

OceanDrop SSC-Addicted Points: 487 More actions · Answer 9

Thanks to all of you who responded and yes, welcome to my daily routine of job application process and interview process! It starts with job advertisement for Entry level developer position with 5-7 yrs of experience must!? And as per LinkedIn, there are already 120-300 applicants and 5-10% of them are with manager level experience?! Your resume will hardly will reach to the hiring manager! It is like a winning a lottery I assume!

Now talk about interview process? It is rather like “interrogation” than interview!

All they try to do is to see how to break you down (try to ask questions till you say I am not aware of any such!) and they do not care if the person’s skills matches to the requirements.

I applied for SQL Developer position and there were 3 technical persons came into the room and without smooth talk and not even introducing what their role is, started asking questions after questions and half of them are nothing to do with requirement! Most of the questions are usual things that you found online and they will not take any other answer other than what they know! It is like answering right answer is not that important but guessing what the interviewer thinks is the right answer is!

As I recently certified as MCP in querying 2012 and with 4+ yrs of experience as developer I thought I would be fine getting any entry level position in bay area. But looks like that is just a fairy tale!

Anyway I am not discouraged (yet) but just surprised how the hiring process works these days! Looks like high supply with low demand is causing all this! I will keep applying and will hope for some decent job will come in my way where I can exercise my skills and learn new skills and new stuff as part of job soon! 🙂 After all, all I know is this and not sure I would enjoy working in any other field!