5 ms·

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)

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.

endersshadow 15y ago
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.
- draz 15y ago
  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....
- reubenyeah 15y ago
  Really? But how did they avoid learning something so basic?
miahi 15y ago
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.
- jmelloy 15y ago
  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.
- fendale 15y ago
  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.
  - LewisCr 15y ago
    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
CWuestefeld 15y ago
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.
- robinhouston 15y ago
  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.
  - mattmanser 15y ago
    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.
  - CWuestefeld 15y ago
    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?

endersshadow 15y ago

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.

draz 15y ago
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....
reubenyeah 15y ago
Really? But how did they avoid learning something so basic?

miahi 15y ago

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.

jmelloy 15y ago
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.
fendale 15y ago
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.
- LewisCr 15y ago
  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

CWuestefeld 15y ago

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.

robinhouston 15y ago
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.
- mattmanser 15y ago
  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.
- CWuestefeld 15y ago
  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?