Slashdot Mirror

← Back to Stories (view on slashdot.org)

How Would You Improve SQL?

Posted by Cliff on from the select-*-except-column99-from-table dept.

theodp asks: "It was the best of languages, it was the worst of languages. SQL's handy, but it can also drive you nuts. For example, if you want all 100 columns from a table, 'SELECT *' works quite nicely. However, if you want all but 1 of the 100 columns, be prepared to spell out 99 column names. Wouldn't it not make sense to provide a Google-like shorthand notation like 'SELECT * -ColumnName' (or DROP=syntax like SAS)? So how would you improve SQL?"

2 of 271 comments (clear)

  1. Hierarchical queries by Bogtha · · Score: 4, Interesting

    Extremely useful when you need to produce a result tree instead of a result list (e.g. Slashdot's nested comments). Oracle does this with "CONNECT BY", there is also a PostgreSQL patch available. Of course there are hacks to do this, such as adding extra fields to keep track of where you are in the tree, but they are a real pain in the arse compared with using the information that's already present in the database.

    --
    Bogtha Bogtha Bogtha
  2. Re:Better NULL handling? by Johnno74 · · Score: 5, Interesting
    I can think of a lot of reasons to have 100 columns, it simply depends on what you're working with - and it is perfectly normalized

    I've got mod points but I just have to give them up to reply to this....

    You're wrong. I agree totally with the grandparent post.
    You DON'T need 100 columns, ever. If you have that many columns you should be breaking the table up into several tables with 1:1 joins. Seriously. There will always be some fields that aren't needed. Make the rows smaller by putting commonly used fields in one table, rarely used rows in the other.

    And your example of a questionaire (1 row per answer, one column per question) is not even close to normalised. What happens if there is a new question? you have to alter the schema. what happens if some questions are not answered? you'd have to have nulls, and wasted space.

    A much better structure is to have a table of questions, and a table of responses (with something like a response id, and maybe an identifier on who answered the questionare) and a question-answer table with each row pointing at a response and a question, and giving the answer that person gave for that question.