8 ms·

"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Becau

by tom_b 15y ago

"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Because the programmers ran all the db access tests against tables with 4 rows of data when the customers would windup with 100 million rows in production. Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice. Because DBA's are (rightly) focused on making sure your db is properly backed up, replicated, and fault-tolerant. They may not even understand SQL queries beyond the basic one table select. Because the users will force data into the systems that the developers never anticipated. Because db jobs are segregated by product for development. If you've done SQL coding on Oracle for the last 10 years, good luck applying for a job doing SQL coding on MS SQL Server. Because SQL hacking doesn't get much respect. I've been in job interviews where deep into the process they ask me a question with data in two tables that requires an outer join. And answering correctly differentiates you significantly from other applicants. I guess it is the "fizz buzz" of SQL these days . . .

endersshadow 15y ago
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?
- bhousel 15y ago
  There's no reason ever to use a right join. I consider those to be a code smell.
  - sesqu 15y ago
    There's about as much reason to use a right join as there is to use a left join.
    - endersshadow 15y ago
      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?
      - ckuehne 15y ago
        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.
        jtheory 15y ago
        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).
    - bhousel 15y ago
- jcromartie 15y ago
  I must take my SQL skills for granted. It really just "clicks" for me, I guess.
  - 3pt14159 15y ago
    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.
- matwood 15y ago
  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.
  - deleted 15y ago
    [deleted]
  - Tyrannosaurs 15y ago
    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.
  - webjprgm 15y ago
    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.
  - bni 15y ago
    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".
- msluyter 15y ago
  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.
- ohashi 15y ago
  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.
  - endersshadow 15y ago
    Jeff Atwood already did it much better than I can: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html http://www.codinghorror.com/blog/2007/10/a-visual-explanatio... Hope that helps!
- bmj 15y ago
  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.
- kahawe 15y ago
  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...
lyudmil 15y ago
I have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at. On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think that's bullshit. No one would use your application without the data, so the database is as much of your responsibility as any other part of the system. You should know how to use it well and should take responsibility for its quality. On the other hand, there are things that belong in application code, rather than, for example, in a stored procedure. Most things you could do with a stored procedure probably belong in application code. The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL. It's much poorer at expressing intent than an OO language would be, it's less readable, and doubles the number of places you need to look for domain logic. The WTF's go both ways.
- pbh 15y ago
  I agree with your point, but with an important caveat. You are right that application developers should not be ignorant of databases, and DBAs should not be ignorant of what application developers are doing. All too often, this is the case. However, the point when people came to think of application and database development as two separate things was the origin of databases themselves, and arguably one of the most important historical points in computer science itself. The notion that data has a life outside of any given application, and that it deserves itself to be managed, was and is incredibly powerful.
- mechanical_fish 15y ago
  How is it that we've come to a point where we think of application and database development as separate things? My take is that the principles of database engineering only make intuitive sense at scale. If every programmer started their career as an assistant DBA at a web host with a lot of traffic, or a data warehouse with a ton of transactions and report generation going on, or a company where lots of different groups were developing client code against the central database, every programmer would be inspired to understand databases. As it happens, programmers start their career building little apps with one client at a time and a handful of data. And many of them finish their career building such apps, because they are very handy. And these programmers may never feel a burning need to know how databases perform with large datasets, or a huge stream of colliding transactions, or a mission-critical data integrity and safety requirement, or a variety of clients all trying to generate complex reports.
mey 15y ago
One of our basic interview questions is doing schema modeling on a white board from business concepts we lob at them.
TruthElixirX 15y ago
"Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice." I am taking a database management class this semester. Its awful. We spent over half the semester drawing diagrams. Just last week we started working with actual SQL.
- LewisCr 15y ago
  Have you been to DB-Class.org? It might make a good supplement to your real-life class.

by tom_b 15y ago

endersshadow 15y ago

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?

bhousel 15y ago
There's no reason ever to use a right join. I consider those to be a code smell.
- sesqu 15y ago
  There's about as much reason to use a right join as there is to use a left join.
  - endersshadow 15y ago
    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?
    - ckuehne 15y ago
      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.
      - jtheory 15y ago
        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).
  - bhousel 15y ago
jcromartie 15y ago
I must take my SQL skills for granted. It really just "clicks" for me, I guess.
- 3pt14159 15y ago
  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.
matwood 15y ago
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.
- deleted 15y ago
  [deleted]
- Tyrannosaurs 15y ago
  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.
- webjprgm 15y ago
  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.
- bni 15y ago
  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".
msluyter 15y ago
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.
ohashi 15y ago
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.
- endersshadow 15y ago
  Jeff Atwood already did it much better than I can: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html http://www.codinghorror.com/blog/2007/10/a-visual-explanatio... Hope that helps!
bmj 15y ago
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.
kahawe 15y ago
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...

lyudmil 15y ago

I have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at. On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think that's bullshit. No one would use your application without the data, so the database is as much of your responsibility as any other part of the system. You should know how to use it well and should take responsibility for its quality. On the other hand, there are things that belong in application code, rather than, for example, in a stored procedure. Most things you could do with a stored procedure probably belong in application code. The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL. It's much poorer at expressing intent than an OO language would be, it's less readable, and doubles the number of places you need to look for domain logic. The WTF's go both ways.

pbh 15y ago
I agree with your point, but with an important caveat. You are right that application developers should not be ignorant of databases, and DBAs should not be ignorant of what application developers are doing. All too often, this is the case. However, the point when people came to think of application and database development as two separate things was the origin of databases themselves, and arguably one of the most important historical points in computer science itself. The notion that data has a life outside of any given application, and that it deserves itself to be managed, was and is incredibly powerful.
mechanical_fish 15y ago
How is it that we've come to a point where we think of application and database development as separate things? My take is that the principles of database engineering only make intuitive sense at scale. If every programmer started their career as an assistant DBA at a web host with a lot of traffic, or a data warehouse with a ton of transactions and report generation going on, or a company where lots of different groups were developing client code against the central database, every programmer would be inspired to understand databases. As it happens, programmers start their career building little apps with one client at a time and a handful of data. And many of them finish their career building such apps, because they are very handy. And these programmers may never feel a burning need to know how databases perform with large datasets, or a huge stream of colliding transactions, or a mission-critical data integrity and safety requirement, or a variety of clients all trying to generate complex reports.

mey 15y ago

One of our basic interview questions is doing schema modeling on a white board from business concepts we lob at them.

TruthElixirX 15y ago

"Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice." I am taking a database management class this semester. Its awful. We spent over half the semester drawing diagrams. Just last week we started working with actual SQL.

LewisCr 15y ago
Have you been to DB-Class.org? It might make a good supplement to your real-life class.