News for hackers. Stuff that matters.
Re:"I've isolated the bug to a database query" by msluyter on Wednesday November 09, 2011 @11:53AM (#3216231)
I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either:

  select * 
    from foo f, bar b
   where f.baz = b.baz (+)
or

  select * 
    from foo f, bar b
   where f.baz (+) = b.baz
But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right vs. left outer join distinction might be unclear to even relatively experienced engineers coming from such an environment.
Re:"I've isolated the bug to a database query" by CWuestefeld on Wednesday November 09, 2011 @04:12PM (#3217411)
This kind of notation (a comma-separated list of tables) used to be the standard. We've moved beyond it for a very good reason: there's a very common class of queries that this notation cannot express.

It's very common to want to LEFT JOIN two tables, and then see specifically which ones on the left had no corresponding record on the right. But the old way cannot specify the JOIN criteria separately from the filter criteria, so we can't express the query.

For example, in my current database, I might do the following to see what categories are not represented by any products:

  SELECT *
    FROM Products.Category AS c 
    LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId
    WHERE p.ProductId IS NULL 
By specifying the JOIN criteria in the ON, I can then use the WHERE to winnow down to the left-only rows. But if that were expressed in the old notation, it would be mistaken for JOIN criteria, and thus all categories would appear to have no products.
Re:"I've isolated the bug to a database query" by robinhouston on Wednesday November 09, 2011 @05:14PM (#3217719)
That’s expressible in Oracle syntax as:

  SELECT *
    FROM Products.Category AS c
       , Products.Product AS p
    WHERE c.CategoryId = p.CategoryId (+)
      AND p.ProductId IS NULL
so I think your point is mistaken. I do agree that the explicit JOIN syntax is clearer.
Re:"I've isolated the bug to a database query" by mattmanser on Wednesday November 09, 2011 @05:46PM (#3217874)
I find the JOIN syntax to be far more maintainable too, the WHERE clauses become really hard to read amongst the dross of boilerplate JOIN clauses from the salient WHERE clauses. Things also get confusing as soon as you throw an OR in the WHERE clause, too many unnecessary brackets.

Also, changing schema tends to be easier too as you can often just delete the JOIN line if you've been coding consistently instead of having to pick through the WHERE clause on more complex queries.

In the end though I guess a lot of it comes down to what you're used to, though I have been exposed to both and would definitely be in the JOIN camp and help utterly destroy you evil ,s if we had to have a worldwide battle to decide the fate of SQL kind.

Re:"I've isolated the bug to a database query" by CWuestefeld on Wednesday November 09, 2011 @08:50PM (#3218488)
That’s expressible in Oracle syntax

Let's modify the query slightly, to retrieve all categories are not represented by any products from a particular manufacturer. The standard syntax would look like this:

  SELECT *
    FROM Products.Category AS c 
    LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId AND p.ManufacturerID = 123
    WHERE p.ProductId IS NULL 
How will you express this in your notation? In particular, what differentiates the "p.ManufacturerID = 123" of the join criteria from the "p.ProductId IS NULL" of the filter criteria?
Re:"I've isolated the bug to a database query" by dangoldin on Wednesday November 09, 2011 @09:58PM (#3218665)
I'm no expert in SQL (Especially the Oracle flavor) but why would the following not work?

SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123

Re:"I've isolated the bug to a database query" by endersshadow on Wednesday November 09, 2011 @12:17PM (#3216363)
I never ask for an explicit left or outer join in an interview. It's always a question, like, "Get me the total sales of every salesperson for this quarter given this schema." And then they return it, and I say, "Can I get the list of all salespeople, even if they haven't had any sales?" And that's where it falls apart, sadly.
Re:"I've isolated the bug to a database query" by draz on Wednesday November 09, 2011 @12:52PM (#3216522)
unfortunately, I must agree with you. 9 times out of 10 candidates cannot answer the question you've posed. One time I even had a candidate with 17 years of experience (!!!) who didn't even know what a join was (masked in a question form). I let it slide, pretended like it was an "advanced" question, and came back to it later (figured he was nervous). The second time around I phrased the question "so, imagine I want to join data from these two tables so that the output would be first name, and location" (2 different tables). Even then, the candidate did not get the hint.... Instead, he offered to change the schema of the fake database and have all the information in one table....
Re:"I've isolated the bug to a database query" by miahi on Wednesday November 09, 2011 @12:48PM (#3216498)
I also find the Oracle outer join operator (+) easier to use.

I just searched the full (Oracle) sql codebase of the project I'm working on, and it seems that there is no clear winner in the "outer join" vs "(+)" battle (outer = 46%, (+) = 54%). There is no code style requirement for joins.

Re:"I've isolated the bug to a database query" by jmelloy on Wednesday November 09, 2011 @02:53PM (#3217100)
I have trouble with the oracle syntax once you get to two or three tables left joined against each other, and consider it more a relic of old oracle programmers than any actual advantage one way or the other.
Re:"I've isolated the bug to a database query" by fendale on Wednesday November 09, 2011 @03:57PM (#3217358)
I think I read recently that using the Oracle (+) operator can give you the error "cannot outer join to more than one table" if you try to outer join to two or more, but the ANSI syntax will allow you to do that - I really should test this out ...

edit - I tried this out and right now cannot get that error, so not sure where I got that idea from.

Re:"I've isolated the bug to a database query" by LewisCr on Thursday November 10, 2011 @10:01AM (#3220237)
This code:

    Where a.client_id = c.client_id
    And a.client_id = b.client_id (+)
    And c.client_id = b.client_id (+)
Doesn't fly in our database