Join Predicate
-
Comments posted to this topic are about the item Join Predicate
--Ramesh
-
nice question. nice and sneaky.
Life: it twists and turns like a twisty turny thing
-
I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?
Thanks & Regards
Sagesh.K
-
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!!!
-
Sneaky.
Reminds me of the advice when reading program code (any language) ...
"Don't trust the comments (or formatting?). Read the code!"
Sagesh (12/13/2007)
I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?Thanks & Regards
Sagesh.K
Worked fine for me in 2K5.
use scratch
--drop table customers
--drop table employees
create table customers (customerid int, name varchar(10))
create table employees (employeeid int, name varchar(10))
insert customers (customerid, name)
select 1,'Dave' union all
select 2,'John' union all
select 3,'Tom' union all
select 4,'Dick' union all
select 5,'Harry'
insert employees (employeeid, name)
select 2,'John' union all
select 4,'Dick' union all
select 6,'Anne' union all
select 8,'Martha'
GO
SELECT *
FROM dbo.Customers
MIDDLE JOIN dbo.Employees ON CustomerID = EmployeeID
Derek
-
What benefit...? Makes you look up middle join in BOL:)
-
Thanks for the valuable information.
I checked the Query like this :
"SELECT * FROM dbo.Customers AS C
MIDDLE 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 C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "
Please provide me some usefull links to know more about MIDDLE JOIN.
Thanks & Regards
Sagesh.k 🙂
-
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).
-
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:
---------------------------------------------------------------------
-
Sagesh (12/13/2007)
Thanks for the valuable information.I checked the Query like this :
"SELECT * FROM dbo.Customers AS C
MIDDLE 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 C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "
Please provide me some usefull links to know more about MIDDLE JOIN.
Thanks & Regards
Sagesh.k 🙂
That's because you've rewritten the query and broken it!
The point is that the AS keyword is optional, so SQL server reads
SELECT * FROM dbo.Customers
MIDDLE JOIN dbo.Employees ON CustomerID=EmployeeID
as
SELECT * FROM dbo.Customers AS MIDDLE
JOIN dbo.Employees ON CustomerID=EmployeeID
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!
Derek
-
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.
-
Pete Brown (12/13/2007)
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!!!
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
SELECT * FROM dbo.Customers
PARTIAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
CONNECTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
DISCONNECTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
UPPER JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
LOWER JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
INVERTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
EQUAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
UNEQUAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
TRIANGULAR JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
INVISIBLE JOIN dbo.Employees ON CustomerID = EmployeeID
And, of course, all these work, but one returns a different result to the others 🙂
SELECT * FROM dbo.Customers
RIHGT JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
RITE JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
WRITE JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
RIGHT JOIN dbo.Employees ON CustomerID = EmployeeID
Derek
-
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?
-
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.
-
Iordan Slavov (12/13/2007)
Had the "as" word been mandatory you couldn't be tricked - after all how much of a saving are 2 letters?That's what I felt when I wasn't getting an expected result and finally noticed the typo in a RIHGT JOIN! 😉
Derek
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply