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).



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • 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.



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • 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