SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ramesh / Join Predicate / Latest Posts

RE: Join Predicate

mayank_sanghvi — Wed, 21 Sep 2011 01:20:30 GMT

Nice Question

RE: Join Predicate

anuradha-600313 — Fri, 30 May 2008 04:53:11 GMT

it is working in sql 2005 also...Have you used the same query OR changed table names?

RE: Join Predicate

HardCoder — Fri, 28 Dec 2007 04:24:30 GMT

Nice twisted question, people had to think once again, i get it wrong although i was knowing that no middle join is there, but do not beyond that it can be alias...

RE: Join Predicate

Mauve — Mon, 17 Dec 2007 09:17:25 GMT

[quote][b]tgarth (12/14/2007)[/b][hr]Why wouldn't you have used a foreign key with cascaded deletes instead?[/quote]I generally do. Triggers are only an exception to the rule. There are certain times when a trigger is necessary due to the application and/or the data model.Examples:- SQL Server [u]2000[/u] doesn't support ON DELETE SET NULL- Often times in a hierarchical structure (i.e., parent -> child relationships) you'll get burnt as the FK constraint is checked at each row deletion. SQL Server doesn't support "deferrable" (check at the end of the transaction) constraint checking like Oracle does.

RE: Join Predicate

Derek Dongray — Mon, 17 Dec 2007 05:31:41 GMT

[quote][b]Tejinder Arora (12/13/2007)[/b][hr]The answer you have posted is wrong I tried it on SQL server and it says "Msg 102, Level 15, State 1, Line 3Incorrect syntax near 'MIDDLE'."[/quote]The only way to get this error is to put an alias on the Customer table. If you use the query as written, 'MIDDLE' is taken as the alias name and no error occurs.

RE: Join Predicate

Tom Garth — Fri, 14 Dec 2007 07:47:09 GMT

I don't know about the unqualified reference, but I do know that the 'deleted' temp table frequently contains rows that are NOT really being deleted, like during updates.Why wouldn't you have used a foreign key with cascaded deletes instead?Tom

RE: Join Predicate

Mauve — Fri, 14 Dec 2007 07:35:07 GMT

The code shows a clear cut example of why columns in a query should always be qualified. I.e., ALWAYS use aliases!I got burnt badly with a DELETE statement in a trigger that was using an unqualified reference to the "DELETED" table with the end result being that more rows than what was intended was being deleted.Using an alias on the table and then qualifing the column will shake out any coding errors, typos, etc. during the parsing phase of the query execution.Example delete trigger: DELETE FROM Foo WHERE FooBar IN (SELECT d.FooBar FROM deleted d)

RE: Join Predicate

Cliff Jones — Thu, 13 Dec 2007 14:24:52 GMT

Had you written the question like this I probably would have gotten it right.SELECT * FROM dbo.Customers MIDDLE JOIN dbo.Employees ON CustomerID=EmployeeIDI guess I did learn that indentation improves readability but I am not sure it was worth the effort to learn what I thiink I already knew. Should not use "Select *" though.

RE: Join Predicate

Iordan Slavov — Thu, 13 Dec 2007 14:14:56 GMT

Strommy :laugh:I like the "random join" the most.

RE: Join Predicate

Tom Garth — Thu, 13 Dec 2007 12:01:13 GMT

At the bottom of the Question / Answer page is a dropdown allowing us to specify how many questions to display on the page. Can we get a number lower than 10?All those RED icons are beginning to depress me.Tom Garth

RE: Join Predicate

TA-472286 — Thu, 13 Dec 2007 11:49:52 GMT

The answer you have posted is wrong I tried it on SQL server and it says "Msg 102, Level 15, State 1, Line 3Incorrect syntax near 'MIDDLE'."

RE: Join Predicate

Strommy — Thu, 13 Dec 2007 09:29:11 GMT

I heard Microsoft is implementing middle joins in 2008. It's for those indiscriminate coders that aren't sure what type of join they want to use. Now you don't have to choose, you can compromise! Next for SQL 2011, the random join, for those who just don't care...Let's get some useful questions. If I saw a programmer put MIDDLE JOIN in their code, I would be tempted to smack them with my keyboard. :DEric

RE: Join Predicate

Derek Dongray — Thu, 13 Dec 2007 09:16:06 GMT

[quote][b]Iordan Slavov (12/13/2007)[/b][hr]Had the "as" word been mandatory you couldn't be tricked - after all how much of a saving are 2 letters?[/quote]That's what I felt when I wasn't getting an expected result and finally noticed the typo in a [B]RIHGT JOIN[/B]! ;)

RE: Join Predicate

Adrian Nichols-360275 — Thu, 13 Dec 2007 08:42:12 GMT

I think that the question is quite useful, especially if you are starting a new DBA job or contract; poorly formatted coding is out there. The author may well feel smug as there are a number of people (including myself) who did not read the code properly and chose an incorrect answer.Life is full of little hurdles that will trip you up; just don't let a slightly damaged ego cloud any further judgement.

RE: Join Predicate

Iordan Slavov — Thu, 13 Dec 2007 08:41:51 GMT

That's a trick question, alright. But don't get so upset you didn't answer it correctly - I didn't either. My thoughts went something like "A predicate for joins that I don't know? Possible but very unlikely... Must be a code error". I didn't question the syntax rules ... But if they allow you to write "bad" code may be the rules are bad? Had the "as" word been mandatory you couldn't be tricked - after all how much of a saving are 2 letters?

RE: Join Predicate

Derek Dongray — Thu, 13 Dec 2007 08:28:55 GMT

[quote][b]Pete Brown (12/13/2007)[/b][hr]I wonder what benefit questions like these have to the community at large.I thought the question of the day was there to inform not just to make the author feel smug!!![/quote]The point is that you have to look at what the code does, especially if the syntax looks new to you.All the following work, and are all equivalent, even though a few get highlighted strangely[code]SELECT * FROM dbo.Customers PARTIAL JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers CONNECTED JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers DISCONNECTED JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers UPPER JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers LOWER JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers INVERTED JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers EQUAL JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers UNEQUAL JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers TRIANGULAR JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers INVISIBLE JOIN dbo.Employees ON CustomerID = EmployeeID[/code]And, of course, all these work, but one returns a different result to the others :)[code]SELECT * FROM dbo.Customers RIHGT JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers RITE JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers WRITE JOIN dbo.Employees ON CustomerID = EmployeeIDSELECT * FROM dbo.Customers RIGHT JOIN dbo.Employees ON CustomerID = EmployeeID[/code]

RE: Join Predicate

Simon Facer — Thu, 13 Dec 2007 08:04:07 GMT

Well, there's 5 minutes of my life I'll never get back! This question has nothing to do with how much you know about T-SQL, just do you pay attention when you are reading bad code. This reminds me of the style of Microsoft certification questions from the 1990's. Lets leave the trick questions where they belong - anywhere else but on this site, or preferably nowhere at all.

RE: Join Predicate

Derek Dongray — Thu, 13 Dec 2007 08:02:18 GMT

[quote][b]Sagesh (12/13/2007)[/b][hr]Thanks for the valuable information.I checked the Query like this :"SELECT * FROM dbo.Customers AS CMIDDLE JOIN dbo.Employees E ON C.CustomerID = E.EmployeeID",so it's showing some error.If i try to join same field name in different tables,that time also i am getting some error.Like : "SELECT * FROM dbo.Customers AS CMIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "Please provide me some usefull links to know more about MIDDLE JOIN.Thanks & RegardsSagesh.k :)[/quote]That's because you've rewritten the query and broken it!The point is that the AS keyword is optional, so SQL server reads[code]SELECT * FROM dbo.Customers MIDDLE JOIN dbo.Employees ON CustomerID=EmployeeID[/code] as [code]SELECT * FROM dbo.Customers AS MIDDLE JOIN dbo.Employees ON CustomerID=EmployeeID[/code]When you rewrote it, you inserted "AS C" after the name of table Customers, so now the word "MIDDLE", which isn't a keyword, is unexpected and you get an error!Because the words "MIDDLE JOIN" have been formatted in upper case and put at the start of a new line, people get fooled into thinking it's a new type of join, when it's just aliasing the Customers table with the name 'MIDDLE'. There is no such thing as a middle join!

RE: Join Predicate

george sibbald — Thu, 13 Dec 2007 04:49:52 GMT

26% got this correct. How many of them got this right by luck, not having realised no such thing as middle join?I am very upset to have missed out on a chance to get a point for getting something wrong :crying:

RE: Join Predicate

Adrian Nichols-360275 — Thu, 13 Dec 2007 04:32:03 GMT

Sagesh,There is no MIDDLE JOIN. As MIDDLE is not a keyword or predicate and there is no alias already specified for tblCustomers, MIDDLE is taken as the alias.Your queries do not work as you have specified an alias for the table (AS c).

RE: Join Predicate

Sagesh — Thu, 13 Dec 2007 03:56:11 GMT

Thanks for the valuable information.I checked the Query like this :"SELECT * FROM dbo.Customers AS CMIDDLE JOIN dbo.Employees E ON C.CustomerID = E.EmployeeID",so it's showing some error.If i try to join same field name in different tables,that time also i am getting some error.Like : "SELECT * FROM dbo.Customers AS CMIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "Please provide me some usefull links to know more about MIDDLE JOIN.Thanks & RegardsSagesh.k :)

RE: Join Predicate

P Jones — Thu, 13 Dec 2007 03:43:17 GMT

What benefit...? Makes you look up middle join in BOL:)

RE: Join Predicate

Derek Dongray — Thu, 13 Dec 2007 03:06:20 GMT

Sneaky.Reminds me of the advice when reading program code (any language) ..."Don't trust the comments (or formatting?). Read the code!"[quote][b]Sagesh (12/13/2007)[/b][hr]I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?Thanks & RegardsSagesh.K[/quote]Worked fine for me in 2K5.[code]use scratch--drop table customers--drop table employeescreate table customers (customerid int, name varchar(10))create table employees (employeeid int, name varchar(10))insert customers (customerid, name)select 1,'Dave' union allselect 2,'John' union allselect 3,'Tom' union allselect 4,'Dick' union allselect 5,'Harry'insert employees (employeeid, name)select 2,'John' union allselect 4,'Dick' union allselect 6,'Anne' union allselect 8,'Martha'GOSELECT * FROM dbo.Customers MIDDLE JOIN dbo.Employees ON CustomerID = EmployeeID[/code]

RE: Join Predicate

Pete Brown — Thu, 13 Dec 2007 03:00:18 GMT

I wonder what benefit questions like these have to the community at large.I thought the question of the day was there to inform not just to make the author feel smug!!!

RE: Join Predicate

Sagesh — Thu, 13 Dec 2007 02:55:10 GMT

I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?Thanks & RegardsSagesh.K

RE: Join Predicate

hodgy — Thu, 13 Dec 2007 02:26:14 GMT

nice question. nice and sneaky.

Join Predicate

Ramesh Saive — Wed, 12 Dec 2007 20:28:56 GMT

Comments posted to this topic are about the item [B]Join Predicate[/B]