3 ms·

SQL was meant to be like readable english, so two or more WHERE didn't made sense. Also select all columns but one may not be future proof is column are added o

• phplovesong 4y ago
  Meh, thats not a valid argument. What happens if i `select id, name from users` and later someone drops the name column. Similarly if i `select * from users` and use name the same problem/bug arises. Dropping a column without the application code refactor is obviously a developer issue, not a SQL one.
  • ilitirit 4y ago
    You're right that it's a "dev/dba problem", but the reason for not using `select !<column>` is exactly the same as not using `select *`. If you specify exactly what you want, there's less of a chance of your ORM-based app breaking because it received an extra or too few columns. I'm sure most people who work with SQL DBs have been bitten this before. Also, while it could also be useful, over the years I've started moving away from things like using `!` to negate things. It makes it slightly harder to read and it ruins the symmetry of my code. And lord knows SQL doesn't need any more of that. It's probably the only language I use often where I have to place my commas at the start of the line; where I need to add a first filter as `1 = 1` etc just to make it easier to comment out things quickly. What I personally want most is the ability to move the FROM clause to the top (this is a no-brainer) and better aliasing rules. The alias I used in my SELECT should be accessible everywhere in the statement.
  • albertopv 4y ago
    Select * is something no one should be doing in real code. Select [list of columns] should the way to query and doing so you know exactly what columns are read. That's one of the reasons I dislike ORM like hibernate. I advocate the least surprises principle, select * or select !column are doors to surprises. What if someone add a geometry column with tons of data (real example happened to me)? You are going to read tons of data that probably you don't need.
    • phplovesong 4y ago
      The query itself does not matter. If i delete/rename a column the app WILL break no matter how much i would follow best practices. I dont see `select !name from users` any worse than `select name from users`. In both scenarios the app will break if the name column is renamed/deleted.
      • beefield 4y ago
        To me the biggest thing against select * is readability. Basically you are obfuscating what you are doing and getting from the database. In trivial queries it does not matter, but when you read more complex procedures, it starts to be a bit annoying.
      • diogofranco 4y ago
        But delete/rename columns are not the only operations, nor the most common. Adding columns is the most common, and there only one of those options breaks
      • rsaarsoo 4y ago
        I think there are pros and cons to both approaches: - Explicitly spelling out the column names has the advantage that you can search your code base for that name and see where it's used. - When declaring a view which filters the rows in a table, using SELECT * would simplify maintenance, as you wouldn't need to update the view when new columns get added to the table.
• wodenokoto 4y ago
  This is a tension between application-sql and exploration-sql. When building a data application, say a reporting tool, you want the code to be explicit. It is much easier to read if all columns are written out. If you are ad-hoc exploring data, this is a pain.
• thesz 4y ago
  >SQL was meant to be like readable english DROP TABLE IF EXISTS and CREATE TABLE IF NOT EXISTS are counterexamples to that statement. Notice that suffix operator IS NULL is present in expression part, but these statements do not use that kind of grammar.
  • albertopv 4y ago
    I was talking about original idea, current Sql evolved and in many ways is a monster, but multiple WHERE instead of AND operator make little sense to me