News for hackers. Stuff that matters.
Re:"I've isolated the bug to a database query" by endersshadow on Wednesday November 09, 2011 @10:37AM (#3215861)
I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview.

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?

Re:"I've isolated the bug to a database query" by matwood on Wednesday November 09, 2011 @11:31AM (#3216133)
I've never understood the typical programmers aversion to query writing. Writing SQL is in general pretty easy (basic set theory).

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.

Re:"I've isolated the bug to a database query" by Tyrannosaurs on Wednesday November 09, 2011 @12:21PM (#3216381)
I think it's easy once you've got your head round the model of data sets but there are plenty of experienced programmers who, due to lack of experience with SQL, still drop back to iterating through large collections of data.

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.

Re:"I've isolated the bug to a database query" by bni on Wednesday November 09, 2011 @04:56PM (#3217634)
Most developers learned in school to program, i.e. writing algorithms. Most development in the "business" world is not that, it is basically managing and transforming data. Something that RDBMSes and SQL is very good at.

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

Re:"I've isolated the bug to a database query" by jaylevitt on Wednesday November 09, 2011 @08:56PM (#3218505)
In four weeks I've gone from not remembering the difference between inner and outer joins to isolating a bug in the Postgres optimizer... and yeah, it's all about daily experience.

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.

Re:"I've isolated the bug to a database query" by webjprgm on Wednesday November 09, 2011 @01:36PM (#3216744)
I learned SQL with copy/paste/modify from PHPBB code base. When I got my first web programming job I could write basic select/insert/update/delete but knew almost nothing of joins. I learned the rest by reading MySQL docs on the job. (Later I also improved my understanding of joins by doing an MS Access project.)

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.

Re:"I've isolated the bug to a database query" by andymurd on Wednesday November 09, 2011 @08:17PM (#3218391)
One of the biggest hurdles that typical programmers must overcome is that SQL is a declarative language, and therefore quite different to the imperative languages they are used to.
Re:"I've isolated the bug to a database query" by gaius on Thursday November 10, 2011 @11:07AM (#3220547)
Imperative programmers all secretly believe in a declarative style, that's why they compile with -O and hope the compiler's "query optimizer" will make it fast for them.
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
Re:"I've isolated the bug to a database query" by bhousel on Wednesday November 09, 2011 @11:02AM (#3215990)
There's no reason ever to use a right join. I consider those to be a code smell.
Re:"I've isolated the bug to a database query" by og1 on Wednesday November 09, 2011 @02:22PM (#3216970)
Agree with this as well. Forgot exactly what one was and googled it to make sure. The example that is used here would indicate orphaned records.

http://www.w3schools.com/sql/sql_join_right.asp

Re:"I've isolated the bug to a database query" by ceejayoz on Wednesday November 09, 2011 @05:58PM (#3217930)
There are valid use cases for orphaned records. A quote database might want to allow registered and anonymous submission, for example.
Re:"I've isolated the bug to a database query" by derefr on Wednesday November 09, 2011 @10:26PM (#3218729)
Or, ala Reddit, comments from deleted users.
Re:"I've isolated the bug to a database query" by sesqu on Wednesday November 09, 2011 @11:48AM (#3216212)
There's about as much reason to use a right join as there is to use a left join.
Re:"I've isolated the bug to a database query" by bhousel on Wednesday November 09, 2011 @12:27PM (#3216408)
Yes, but one of them means that you're thinking about the problem backwards.
Re:"I've isolated the bug to a database query" by endersshadow on Wednesday November 09, 2011 @12:14PM (#3216345)
I don't think I've ever used a right join in a production query (perhaps a couple of times when I was doing some digging and it was easier), but I don't understand your statement. Left joins are insanely useful. For example, getting all the sales for a salesperson:

    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?

Re:"I've isolated the bug to a database query" by ckuehne on Wednesday November 09, 2011 @12:27PM (#3216409)
What he (probably) means is that the following query returns the same result as your query:

    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.
Re:"I've isolated the bug to a database query" by jtheory on Thursday November 10, 2011 @07:50AM (#3219862)
That's what was meant, I assume, but it's misguided. Right joins don't create a "code smell" for technical reasons, but for practical reasons. Because left joins are the standard approach, if you're using a non-standard approach to a common problem you'd better have a damned good reason (because you're just confusing maintenance programmers... and thus causing errors... with no justification, otherwise).
Re:"I've isolated the bug to a database query" by gnaritas on Wednesday November 09, 2011 @11:55AM (#3216242)
Ditto.
Re:"I've isolated the bug to a database query" by bmj on Wednesday November 09, 2011 @02:44PM (#3217068)
When I interviewed for my current position (which involves database design/query writing/storage/etc), the interview was heavily SQL-focused. My would-be manager started with very easy, general questions, and then continued to plumb the depths of my knowledge 'til he hit bottom (took about 30 minutes[1]). He is of the opinion that if you can think on your feet about general database design and set theory, you're a pretty competent programmer for the work the company does.

[1] Though I apparently did well enough to get the job, I felt as if I utterly failed the interview.

Re:"I've isolated the bug to a database query" by jcromartie on Wednesday November 09, 2011 @11:27AM (#3216112)
I must take my SQL skills for granted. It really just "clicks" for me, I guess.
Re:"I've isolated the bug to a database query" by 3pt14159 on Wednesday November 09, 2011 @12:46PM (#3216486)
Me too. Most programmers don't understand the "brain" of a select statement. People gtalk me all the time when they are stuck and the answer is often times to remove 90% of the query and replace it with a GROUP BY and a HAVING. Most people don't think of the steps the interpreter goes through because it is a declarative language.
Re:"I've isolated the bug to a database query" by ohashi on Wednesday November 09, 2011 @12:31PM (#3216427)
Can you explain each of them, when they are typically used and why they matter? I've used them before but honestly, I am not that comfortable with them and don't ever think about using them.
Re:"I've isolated the bug to a database query" by endersshadow on Wednesday November 09, 2011 @01:53PM (#3216815)
Jeff Atwood already did it much better than I can:

http://www.codinghorror.com/blog/2007/10/a-visual-explanatio...

Hope that helps!

Re:"I've isolated the bug to a database query" by kahawe on Thursday November 10, 2011 @05:28AM (#3219620)
I cannot help but think that the recent flavor-of-the-month rush to all that NoSQL is nothing but the average web monkies who do not understand databases, SQL and the fundamental problems RDBMS are dealing with so, yea, when I have no idea about all that I would prefer a network-accessible key-value map as well...