I'm guessing it's all about what people have experienced. I have been coding against RDBMSes since my first job while still in college. I have even read Celko for fun at one point :)
IMHO, learning the hard parts of SQL only come from experience with particular RDMSes and dealing with large datasets. For example, knowing that the only way to get a query to run against a large Teradata table without running out of spool space is to create and use temp tables only comes as a result from trying to run the query.
It's the fastest way for them to solve a problem in terms of development effort, but it often leads to poor solutions and poor SQL.
The result is that many developers will do selection and sorting algorithmically (taking the bugs and LOC that entails), instead of using declarative SQL.
I have worked at places were the database is just seen as something in the periphery, that you just have to deal with since Oracle is a requirement from customers. Its sick really when you think about it.
Im disappointed by many of the comments here that basically is "SQL sucks because I don't understand it".
I have no formal CS schooling, so thinking in sets is far from basic - I often have to stop myself from thinking in iteration. Reading Celko's "Thinking in SQL" book is in fact helpful (although why does he keep yelling at me? and what's FORTRAN?)
But it's very foreign and black-box-y to many programmers, and a tiny bit of SQL voodoo, like a tiny bit of Javascript voodoo, will get you a very long way with very poor performance.
So SQL is just as easy to pick up as anything else, but you're going to be horrible at it at first and need to get past the mental block of seeing something so different conceptually to "normal" programming.
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.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. 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.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.
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?SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123
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.
edit - I tried this out and right now cannot get that error, so not sure where I got that idea from.
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 select
sp.Name,
sum(s.Amount) as TotalSales
from
SalesPerson sp
left join Sales s on
sp.SalesPersonKey = s.SalesPersonKey
group by
sp.Name
order by
TotalSales desc
If you did a correlated subquery, it would take a ton of time to complete (`select sum(Amount) from Sales s where s.SalesPersonKey = sp.SalesPersonKey`), especially on large tables.Left joins (and full outer joins) are plenty useful and I use them almost daily. Care to explain what you mean?
select
sp.Name,
sum(s.Amount) as TotalSales
from
Sales s
RIGHT join SalesPerson sp on
sp.SalesPersonKey = s.SalesPersonKey
group by
sp.Name
order by
TotalSales desc
EDIT: Formatting.[1] Though I apparently did well enough to get the job, I felt as if I utterly failed the interview.
http://www.codinghorror.com/blog/2007/10/a-visual-explanatio...
Hope that helps!
I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that doesn't tell you much."
Relational databases are oft-misunderstood and underestimated time and time again. Where are the Codds of yesteryear?