[HN Gopher] A Critique of SQL, 40 Years Later
___________________________________________________________________
A Critique of SQL, 40 Years Later
Author : mariuz
Score : 142 points
Date : 2022-08-24 12:48 UTC (10 hours ago)
(HTM) web link (carlineng.com)
(TXT) w3m dump (carlineng.com)
| thomas536 wrote:
| I once read an article about SQL and how reordering the sections
| of a query would make it more ergonomic for users (iirc things
| like specifying what you want first and then how to present it
| last, more like a pipeline). I searched many times over the years
| but have not been able to find it again.
| colinmcd wrote:
| Perhaps this one?
|
| https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
| bearjaws wrote:
| The only thing I would really blame solely on SQL is that UPDATE
| and DELETE statements don't require you to specify a limit.
|
| I have seen many times in my career where a rogue delete just
| truncates a table, a simple statement of intent (e.g. LIMIT 1)
| would tell the query planner that if it is about update/delete
| more than 1 row, it should error. In fact MySQL actually returns
| a warning if you do this.
|
| TRUNCATE clearly states your intention to delete everything, but
| DELETE by default also deletes everything.
|
| There were also definitely some bad paradigms invented as a
| result of SQL (e.g. all business logic lives in the database as
| stored procedures or stored functions), but that was bound to
| happen whenever a shiny new tool comes out and engineers want to
| use it.
| Shorel wrote:
| Require?
|
| Some UPDATEs don't allow me to specify a limit!
|
| Infuriating stuff.
| merb wrote:
| > TRUNCATE clearly states your intention to delete everything,
| but DELETE by default also deletes everything.
|
| btw. they are completly different.
|
| TRUNCATE basically ignores all transaction semantices for the
| sake of performance (which can be really really bad) in mysql
| it can't be rolled back, in postgres it's not mvcc safe.
| TRUNCATE most often uses either an exclusive lock or some other
| mechanism.
|
| if you know what you are doing truncate can be helpful, but
| delete has its benefits as well and truncate should be avoided
| by beginners.
| dspillett wrote:
| _> TRUNCATE clearly states your intention to delete everything,
| but DELETE by default also deletes everything._
|
| I think the other way around.
|
| "DELETE Something" to me means get rid of the whole of
| _Something_. "TRUNCATE Something" (in general English, not SQL)
| to me means to make something shorter, which probably doesn 't
| mean removing it completely.
|
| Perhaps "EMPTY Table" would have been a better choice than
| "TRUNCATE Table", but by the time it reached the standard1
| TRUNCATE had long since been picked and until then it was not
| the responsibility of the SQL standard: you only had DELETE and
| without a filter it makes sence that this deletes all.
|
| ----
|
| [1] note that TRUNCATE was not in the SQL standards until
| SQL2008, long after it was common in various DMBSs as a
| minimally logged alternative to DELETE-without-filtering-
| clauses.
| snowstormsun wrote:
| https://youtu.be/lS7zNbB-XfI?list=PLn0nrSd4xjjaSLBSzmno-3Ods...
| gibsonf1 wrote:
| I think the issue isn't SQL, but the table paradigm for storing
| data. Humans do not store data in separate tables that need
| joining, they store data in a fully connected graph (hyper-
| graph). Its about relationships and hierarchies - the graph
| allows incredibly fast hierarchical reasoning, as most things
| involve hierarchical reasoning. The relational table, and Sql by
| correlation, are terrible at the human approach to working with
| data especially hierarchies.
| warrenm wrote:
| I work extensively with Splunk which is dominantly based on
| noSQL underneath (MongoDB, among other, proprietary
| technologies)
|
| I've also recently been [re]introduced to graph databases
| (which are highly similar to the pre-relational network
| database paradigm)
|
| You can simulate graph relationships with an RDBMS or noSQL -
| but you shouldn't
|
| You can simulate an RDBMS with a graph db or noSQL - but you
| shouldn't
|
| You can simulate noSQL with graph and RDBMS tools - but, again,
| you shouldn't
|
| They all have their place - and ca even work quite well
| together, if you use them as they're intended
| gibsonf1 wrote:
| But thats just it, having many silos for connected
| information is just not the way we humans do it, nor
| necessary. A single conceptually indexed space/time
| hypergraph (as we humans do it) for any part of the world is
| all you need, scalable up to many billions of edges for that
| one part of the world.
| tremon wrote:
| _having many silos for connected information is just not
| the way we humans do it_
|
| What do you mean? I can think of many silos containing
| connected information: municipal residence records,
| marriage registries, birth records, police reports, tax
| records, medical records (for each hospital) are all silos
| connected by a citizen's identity.
| gibsonf1 wrote:
| You missed the "humans" part of my statement. We humans
| mentally have a fully linked graph (hypergraph) of what
| we know through space-time and conceptually indexed. We
| do not have a whole bunch of disconnected data silos in
| our minds. Modelling the very successful human approach
| to data and reality modeling may make more sense then
| using these 50 year old tables and disconnected silos?
| johannes1234321 wrote:
| > You can simulate noSQL with graph and RDBMS tools - but,
| again, you shouldn't
|
| What is the feature which makes the noSQL which you shouldn't
| do in a relational database? To me noSQL always looks like a
| subset of relational database. The only thing, maybe, is that
| you can truly put everything in, but with modern JSON
| features and all the other things I don't see a downside in
| using relational. (Except a little learning curve, while that
| can be hidden behind some ORM or something if you really want
| for the beginning)
| Shorel wrote:
| It's the other way around.
|
| We use tables because they help us to massively simplify the
| processing of data.
|
| Do you want to deal with graphs? The algorithms involved are
| harder than what your intuition is telling you.
|
| In fact, the first databases, before relational databases were
| in vogue, were hierarchical/network databases.
|
| https://en.wikipedia.org/wiki/CODASYL
|
| They were obsoleted and replaced when RDMS appeared.
| gfody wrote:
| sql doesn't specify how data is stored, it's only a description
| of the data you want. how your data is connected or not at rest
| is up to the engine and how you leverage its engine-specific
| features.
| naranha wrote:
| My problem with SQL is that it's 99% Excel and 1% A database
| query language. And the boundaries between are often not well
| defined. Why even have a SUM function, when it is not accelerated
| by an index, that makes using it automatically non-scalable. I
| think there should be a core language, perhaps similar to SQL
| perhaps not, as an interface to pure DB functionality, all the
| other 99% could be done in Excel and while it's nice to have, it
| should be more clearly separated.
| nhumrich wrote:
| Perhaps excel is 99% SQL and not the other way around,
| especially since SQL is older.
| acdha wrote:
| > Why even have a SUM function, when it is not accelerated by
| an index, that makes using it automatically non-scalable.
|
| Because it's useful and that last part isn't true? Databases do
| more than indexes and you have trade offs regarding the impact
| of adding too many indexes.
|
| It's also not only not a problem for scalability but in reality
| an important way to _improve_ scalability. Consider a simple
| example where we do "SELECT SUM(price) FROM orders GROUP BY
| customer_id". Removing SUM() from the language would massively
| increase the amount of data which needs to be processed into
| the response -- which makes anything else you're doing like
| sorting harder - and it prevents various optimizations the
| database engine might make. For example, on AWS RDS Aurora the
| database pushes all of that down to the storage nodes so each
| storage node will return the sums for the records which are on
| that node and the main node can sum those intermediate values
| without needing to transfer a single source row over the
| network. Since SUM() is part of the language and well-defined,
| that's safe for one database team to implement without the risk
| of their results not matching a competing database.
|
| Other things to think about are computed views and stored
| procedures: in both cases, there are situations where these are
| dramatic performance improvements or otherwise desirable and
| having a richer language means that those constructs can solve
| more problems.
| cmrdporcupine wrote:
| _" Despite the improvements listed above, in a 2014 interview, CJ
| Date said "we didn't realize how truly awful SQL was or would
| turn out to be (note that it's much worse now than it was then,
| though it was pretty bad right from the outset)." This quote
| leaves me wondering - if Date himself were to write an updated
| critique, what would it look like? My best guess is most of his
| criticism would revolve around further departures of SQL from the
| relational model, but specific examples escape me."_
|
| I am not sure how the author has missed the copious amount of
| material that Date (& Darwin) wrote on this topic? The obvious
| one being the Third Manifesto (whole book available here [1]
| since it's out of print now). But even _" Database in Depth"_ [2]
| has extensive discussion about this despite being a less
| "opinionated" and "ranty" book (highly recommended BTW)
|
| Yes, not explicitly updated since 2014, but there's really
| nothing new to say since then?
|
| Date is definitely concerned about the departures from the
| relational model in SQL. But it's (EDIT) not just about purism.
| He's also pretty in general peaved off at SQL for similar
| concerns that the author presents here about syntax and
| expressiveness. Third Manifesto's "tutorial D" mandates the
| addition of a richer type system, including higher level types
| that match on relation constraints, and "operators" (similar to
| OO methods) on those types; basically a kind of predicate
| dispatch.
|
| I imagine he's pretty sick of being ignored.
|
| [1] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf
|
| [2] https://www.oreilly.com/library/view/database-in-
| depth/05961...
| fipar wrote:
| I agree with everything you're saying, though I'm assuming
| here:
|
| > Date is definitely concerned about the departures from the
| relational model in SQL. But it's just about purism.
|
| Perhaps you meant to say "But it's not just about purism"?
| cmrdporcupine wrote:
| Oops, yes, thanks for the catch. Editing.
| exabrial wrote:
| The only thing that affects my daily life with SQL is that FROM
| should be before the SELECT keyword. This would _greatly_ improve
| type-ahead support in SQL IDEs.
|
| Nothing is perfect, but that is really the main beef. Another
| commentor already nailed having a LIMIT WITH ERROR clause to be
| specified on UPDATE,DELETE statements and explicitly throw an
| error otherwise.
|
| SQL is on of my favorite tools to use and I don't see it getting
| replaced by anything any time soon.
| toto444 wrote:
| Every time SQL is mentioned on HN someone comes to complain
| about FROM coming after SELECT. I use SQL every day and not a
| single time have I found reason to complain about it. Can you
| give a bit more detail about what's wrong with it being like it
| is ?
|
| EDIT : thanks all for your reply. I now understand that it is
| an IDE related thing not something fundamental to the language.
| alexvoda wrote:
| As sibling comments have mentioned, this is a valid
| complaint. It is useful to remember that SQL is an old
| language already, and there are plenty of warts that in all
| of this time have been observed. The same way C is old, and
| there are things to be apreciated about fresh attempts like
| Zig, Rust, Swift, Nim.
| [deleted]
| Hinrik wrote:
| Like the commenter alluded to, it allows accurately
| constrainted type-ahead. If a query starts with "SELECT FROM
| my_table " and expects one or more column names at that
| point, your IDE can already suggest the column names from
| my_table (and not from any other table).
| emaginniss wrote:
| Everyone else is mentioning it from an IDE perspective, but
| let's also think about logically from a language perspective.
| When you start a FROM clause and add some JOINs, a few WHERE
| conditions and maybe GROUP BY, you are building a virtual
| view of a series of tables, columns, and aggregations. You
| could even define this data set as an ephemeral table. What
| you do with that data set afterwards might vary depending on
| the need, but the data set might not change. Depending on the
| application, you might select different columns from the data
| set. We do this naturally using a WITH clause at the
| beginning of a query.
|
| WITH (combine a whole bunch of stuff) as dataset SELECT a, b,
| c FROM dataset
|
| This approach just says:
|
| FROM tables... WHERE ... SELECT a, b, c
|
| To me, it does make a lot of sense. This is also the paradigm
| that some of the graph databases use.
| spullara wrote:
| It makes way more sense honestly. It also looks a lot like
| a functional pipeline if you set it up that way.
| tremon wrote:
| _You could even define this data set as an ephemeral table_
|
| Exactly! This is where SQL hurts me the most: not being
| able to store (partial) query expressions in variables for
| later reuse. The only way to do this is by creating
| explicit views (requires DDL permissions) or executing the
| partial query into a temporary table (which is woefully
| inefficient for obvious reasons).
| SideburnsOfDoom wrote:
| Agreed, it is not just an "IDE related thing", it is also a
| logical arrangement of thoughts thing, a readability thing.
| kaba0 wrote:
| And as another commenter noted, the underlying relational
| algebra is also not in agreement, so it is definitely not
| logical. I believe they wanted to mimic human language
| statements, but that goal hurt more than it helped.
| cogman10 wrote:
| Imagine a table `Foo` with the schema
|
| `FooId, name, date, favorite_color, active`
|
| Now, you want to pull the ID for the latest `foo` for a
| specific date, but you don't know any of the column names.
|
| The modern workflow looks like this
|
| You write
|
| `SELECT * FROM Foo`
|
| then you say, "Ok, now I can get autocomplete"
|
| `SELECT FooId FROM Foo`
|
| "Ok, now I can write the where clause"
|
| `Select FooId From Foo WHERE date=?`
|
| It becomes an exercise in moving the cursor around just to
| get the autocomplete going.
|
| If you are really familiar with the schema, not a problem.
| But if you just remember a few details about it, then you are
| stuck in this weird back and forth cursor moving thing.
|
| That's why it'd be more ergonomic to have something like
|
| `From Foo Select FooId where date=?`
|
| Because you never need to move your cursor and you could get
| all the autocomplete you need at the right times.
|
| This becomes especially true when writing joining statements
| FROM Foo f JOIN Bar b ON f.FooId = b.FooId
| SELECT f.FooId WHERE b.active = 1
| spprashant wrote:
| If you exploring a set of tables you have never touched
| before, it really neat if you could just type in:
|
| FROM tablename t SELECT t.<press tab>
|
| and some form of autocomplete mechanism, either prefills all
| the column names from table "t" or suggests the list of
| columns and/or types associated with it.
|
| This is much better than having to: 1. Run a SELECT with
| LIMIT statement just to get an idea of the layout. 2. Point
| and click through the IDE treeview.
|
| Honestly, I don't think it helps a whole lot beyond this
| functionality, but I can see why folks who are accustomed to
| thinking in functional pipelines (from -> select -> map ->
| filter -> collect) can prefer this way of querying.
|
| I think PRQL is one attempt at building something this
| way.[1]
|
| [1]. https://github.com/prql/prql
| noirscape wrote:
| DESCRIBE TABLE is a command that pretty much does exactly
| this (explain what a table contains) and it's a part of
| MySQL.
|
| If you use PostgreSQL then you can use \d instead.
|
| I'm sure the other RDBMSes have their own equivalent
| (except for maybe SQLite but if you're using SQLite outside
| of toy environments or hobby projects, you're doing it
| wrong).
| holri wrote:
| in sqlite3 it is .schema
| __float wrote:
| This may be true, but you've missed the point that this
| is about aiding autocompletion while you're writing the
| query the first time.
| jjav wrote:
| > except for maybe SQLite
|
| .schema
|
| > if you're using SQLite outside of toy environments or
| hobby projects, you're doing it wrong
|
| That's an uninformed statement. SQLite is extremely solid
| production quality code. Of course it's not the
| universally applicable database solution, nothing is.
| Sometimes you need Cassandra or its kind, often
| MySQL|Postgres, other times SQLite is the correct answer.
| tremon wrote:
| You're missing the point: until you have typed the FROM
| TableName t in your IDE, SELECT t.<press tab> can not do
| autocompletion. That has nothing to do with whether SQL
| supports querying metadata (the ANSI portable way would
| be through INFORMATION_SCHEMA btw, not DESCRIBE). It's a
| consequence of the brain not responding to think-ahead
| queries by the IDE.
| ldldldk wrote:
| SQLite absolutely has production level applications, it's
| much more than a toy.
|
| https://www.sqlite.org/whentouse.html
| andy81 wrote:
| Every RDMBS has this, including SQLite.
| The_Colonel wrote:
| It's kind of funny to see a claim that the most widely
| deployed database in the world is useful only for toy
| projects.
|
| https://www.sqlite.org/mostdeployed.html
| bot41 wrote:
| sheesh lets hope there are no vulnerabilities in there
| bmacho wrote:
| I guess there are more toy projects than serious ones.
| andylynch wrote:
| In SSMS and almost certainly many other SQL editors
| Intellisense works on the select list as soon as you have a
| working from clause, the only caveat being you can't
| autocomplete columns reading when writing a statement
| purely left to right.
|
| But I think that's not a big deal at all, and the SQL
| approach has a certain advantage in putting the type of
| action (select/update/delete/drop etc) front and centre,
| which is really quite helpful.
| tomjakubowski wrote:
| SELECT FROM widgets VALUES id, factory_origin_id,
| frobbable_knobs
| baq wrote:
| you seem to be too used to it to notice...
|
| you start to type SELECT some, columns and can't get
| autocomplete until you add FROM afterwards, so you either
| type the query inside out (SELECT FROM table and go back to
| after SELECT) or just give up.
| mamcx wrote:
| > I now understand that it is an IDE related thing not
| something fundamental to the language.
|
| No, is _fundamental issue_ to the language!
|
| The relational model is clear. You START with a relation and
| then compose with relational operators that return relations.
|
| ie: rel | project
|
| Sql do it _weird_. Is like in OO, where instead of define a
| class THEN define the properties, you define the properties
| THEN define the class.
|
| And this _fundamental_ issue with the language goes deeper.
| The rules are ad-hoc for each sub-operator despite the fact
| using relational model MUST make it simply to compose.
|
| So, you have rules for HAVING, GROUP BY, ORDER BY, WHERE,
| SELECT and so on and none are like the others, are different
| in small but annoy ways...
| wwweston wrote:
| Having learned Prolog before SQL, it was weird when it
| clicked that both were relational languages, but SQL
| decided to hide that underneath a natural language facade
| and the inconsistencies that come with it.
| jolmg wrote:
| Having SELECT come first makes sense to me because it's the
| only part of the statement that's required. FROM and
| everything else is optional.
|
| Also when reading a statement, you're mostly interested in
| what the returned fields are rather details like where they
| came from or how they're ordered. It kind of makes sense to
| put it at the start.
|
| Maybe other syntax forms have their benefits, specially
| when writing, but I don't think SQL's choice is completely
| senseless either.
| tadfisher wrote:
| SELECT itself should be optional. Languages with
| expressions are fairly intuitive, e.g. "int x = foo.bar;"
| where "foo.bar" is equivalent to the "SELECT bar FROM
| foo;" SQL statement. I don't breathe SQL every day, so
| I'm struggling to come up with a case where removing
| SELECT results in parsing ambiguity.
| jolmg wrote:
| You mean the keyword; I meant the clause. "FROM foo" is
| optional to the syntax.
| mamcx wrote:
| > I'm struggling to come up with a case where removing
| SELECT results in parsing ambiguity.
|
| Oh! Is super-ambiguous! Make the parser and enjoy it!
|
| Lets make this more concrete: city
| SELECT id city ORDER id city id <--
| Order or select???
|
| You could then "favor" projection as the most important
| than the others. Ok, so: city city
| city
|
| Which is the table, or the field?
| toto444 wrote:
| > I'm struggling to come up with a case where removing
| SELECT results in parsing ambiguity
|
| It's actually useful to the person reading the code. It
| clearly defines where a statement starts, what it does
| and makes reading a query close to reading English. Show
| a SELECT FROM WHERE query to someone who does not know
| SQL and the person will understand it. It might be a bit
| harder if you remove the SELECT.
| tremon wrote:
| _It clearly defines where a statement starts_
|
| And this is another example of the ad-hoc problems of
| SQL: query terminators (;) are optional. If they weren't,
| there would be no abiguity where a statement would start:
| it's the first word after the previous terminator.
| mamcx wrote:
| > SELECT come first makes sense to me because it's the
| only part of the statement _that 's required_.
|
| Only *IN SQL*.
|
| You don't need it on the relational model, heck, no even
| in any other paradigm: 1
|
| That is!. (aka: SELECT 1)
|
| So this: SELECT * FROM foo
|
| is because SQL is made _weird_. More correctly, this
| should be only: foo
|
| Also, SELECT is not required all the time, you wanna do:
| foo WHERE .id = 1 foo ORDER BY .id foo
| ORDER BY .id WHERE .id = 1 //Note this is not valid in
| SQL, but because SQL is wrong!
|
| But you probably think this as weird, because SQL in his
| peculiar implementation, that is, _ok_ for one-off, ad-
| hoc query, and in THAT case, having the list of fields
| first is not that bad.
|
| But now, when you see it this way, you note how MUCH
| nicer and simpler it could have been, because then each
| "fragment" of a SQL query could become *composable*.
|
| But not on SQL, where the only "composition" is string
| concatenation, that is bad as you get.
| isitmadeofglass wrote:
| > thing not something fundamental to the language.
|
| It is fundamental to the language. The evaluation order is
| from,where,group by, having, select, order by, limit.
|
| Everything in perfect order is, select except.
| guenthert wrote:
| We have transactions to prevent mishaps, no?
| BeefWellington wrote:
| I think this stems from a misunderstanding of the entire point
| of SQL. It isn't about looking at data in an individual table,
| it's about retrieving a _Result Set_ for complex queries.
|
| All the _FROM-first_ examples I 've ever seen are almost
| universally the simplest query in the world where autocomplete
| is not a large hurdle anyways because you aren't even bothering
| with table aliasing. As soon as you do anything even moderately
| complex (multiple joins, subqueries, calculations, etc.) the
| advantage of putting FROM first vanishes, and if you're adding
| a table alias or hard reference to the table you can _already_
| see what is in the SELECT list _AND_ in many cases get
| autocomplete.
| isitmadeofglass wrote:
| > I think this stems from a misunderstanding of the entire
| point of SQL. It isn't about looking at data in an individual
| table, it's about retrieving a Result Set for complex
| queries.
|
| No misunderstanding at all. It's just more natural to first
| describe what the sources of the data are, joins etc, and
| then afterwards which columns you'd like to retrieve, or what
| calculations you'd like to perform etc.
|
| The current way of having select first is just plain dumb.
| When it comes to reading order, you never actually know what
| is selected until you've read through the from, where, group
| by and having clauses anyway, so you constantly have to jump
| back and forth between dart and end to see the context. And
| it also better matches the sql evaluation order to have from
| first and select is after all of these.
|
| Select also just better fits with order_by and limit since
| it's also about restricting results after you've gather them
| all up and stitched them together etc.
| vivegi wrote:
| Several decades ago while using Oracle and SQL*Plus, I used to
| generate the column list for queries from the data dictionary
| table (ALL_TAB_COLUMNS). Once I learnt that trick, I never
| typed the column list ever again. Eventually, I had a library
| of queries for common tasks. You could use that trick with
| almost all database engines.
|
| IDEs were never a favorite (they were quite limited then).
|
| Today, the situation is a lot better.
| taeric wrote:
| You could improve type ahead by just pouring effort into the
| IDE. Especially with the amount of resources we have nowadays.
| Easy enough to have basic typeahead on basically all possible
| columns when writing the select, and then you could use the
| columns as a filter on the tables during auto complete.
|
| In general, this isn't done. But I don't see any technical
| reason it can't be done.
| chrsig wrote:
| This strikes me as solving the wrong problem. It's not as
| useful to be able to derive a table from selected columns --
| the desire is to complete columns from a table. It's both
| more intuitive and helps the user more than once.
| taeric wrote:
| I disagree. Folks often know what column they want, and
| have to find the table that best gives it.
| refactor_master wrote:
| If you have multiple tables, how do you avoid suggesting a
| wrong column, before filling out the table name?
|
| You could suggest _all_ columns up front, and then afterwards
| tell the user "this suggestion doesn't exist", which would
| just erode trust in the autocomplete.
| kaba0 wrote:
| Start typing "SEL", ide suggests "SELECT _ FROM", you press
| enter and the above text is entered, while the cursor is
| placed after from. You write that part of the query and
| after pressing enter it will jump back to the select part.
|
| This is done already by Jetbrain's datagrip for example.
| taeric wrote:
| Suggest all. And with modern UIs, you can hint the table
| with the column.
| camgunz wrote:
| You can just put SELECT * FROM stuff and then edit your columns
| later.
| [deleted]
| mordechai9000 wrote:
| Maybe we should treat SQL like JavaScript, and use it as a
| compiler target instead of coding in it directly. /s
| qorrect wrote:
| I know you're being sarcastic, but yes!
| dagss wrote:
| Not sure why you added /s; .. SQL is in many ways exactly
| lile JavaScript. The only way to run your code where it needs
| to run, but with some things to be desired language wise.
|
| I wish more languages compiling to SQL were more common.
|
| (Not ORMs though they just miss the point entirely..)
| ilkhan4 wrote:
| I mean, that's pretty much what ORMs do, right? Hasura et al
| too.
| mordechai9000 wrote:
| Yes, this is true. Although my experience with ORMs is that
| there is always a reason to use some kind of escape hatch
| to run raw SQL directly.
| d0mine wrote:
| Configure your IDE, to help you write SELECT queries. It should
| be easy to insert a template with jump points in the desired
| order e.g., a generic YASnippet in Emacs should be able to do
| it.
|
| Thus by the time you are writing the column expression after
| the SELECT, the FROM table expression would be filled already
| (enabling scope-dependent column completion).
| crazysim wrote:
| I don't know why my memory jogged to the old ruby on rails
| screencasts but I suppose there's nothing stopping IDEs from
| jumping to the FROM section of a query and then returning you
| back to the SELECT in some sort of "macro"/snippet. It's a
| hack, I guess.
| kaba0 wrote:
| That's exactly what Jetbrain's Datagrip does.
| mordechai9000 wrote:
| From what I've seen, it will initially populate
| autocomplete column name values from all the introspected
| tables on the current schema search_path, even before the
| FROM clause is added. Usually this is good enough for me,
| so I don't think about it. I mostly interact with postgres.
| mmcdermott wrote:
| That's a fair point. This comment made me realize my own
| tendency to write "select * from..." so that I could supply the
| "from" before going back and replacing "*" with specific
| columns.
| tehlike wrote:
| Check this out https://github.com/prql/prql
| qorrect wrote:
| Love PRQL :D
| stickfigure wrote:
| I started my career in the 90s writing ROLAP engines, and even
| though I've spent most of my time since doing "web"
| development, I still seem to end up having to build engines
| that generate SQL queries that are dozens of lines long.
|
| The complaints about SQL composability are real. The grammar is
| fundamentally pretty irregular. Acceptable for humans to type
| ad-hoc, crappy for computers to generate.
|
| You can like what SQL does for you (I do!) but it's also easy
| to imagine something a bit better. I thought the article was
| spot-on. I hope some future SQL x.0 will fix these issues, but
| also be similar enough to present-day SQL that I don't have to
| learn a whole new language from scratch.
| Koshkin wrote:
| FWIW in LINQ, Select comes after From and Where.
| jiggawatts wrote:
| Also in Kusto Query Language (KQL), which is used extensively
| in Azure.
| sedatk wrote:
| Yeah, Select comes last, and it makes the most sense.
| [deleted]
| tibbydudeza wrote:
| I wish there was some smart machine learning technology that will
| take my shitty SQL developer queries and make it more performant
| - sometimes it is like sitting in front of a bubbling cauldron
| and invoking magic incantations.
| dominotw wrote:
| SQL is having somewhat of a moment in the bigdata world, thanks
| in part to 'modern datastack' and new age datawarehouses like
| snowflake,bigquery.
|
| However there are a lot of pushback from 'traditional'
| dataengineers who were trained on spark/scala. Its bit of
| hardsell to go from a highly typed language to a free for all
| text based logic.
|
| I think the following is needed for sql to be finally accepted as
| 'serious' contender.
|
| create compiled sql language ( not pandas)
|
| 1. that compiles to sql and addresses some of the issues bought
| up in the post like nested aggregations.
|
| 2. make code reusable. Eg: apply year over year growth to a table
| that has the requisite columns. Compiler should check this in
| ide.
|
| 3. make materializations first class concept in the language. No
| seperate dbt layer.
|
| 4. crate a way to package and distribute libraries that you can
| import into your project .
|
| 5. a unit testing framework that makes it easy to test the logic
| without having to setup test tables in the database.
| eatonphil wrote:
| > I think the following is needed for sql to be finally
| accepted as 'serious' contender.
|
| Whatever way you slice it, SQL is one of the most used
| languages today [0].
|
| [0] https://spectrum.ieee.org/top-programming-languages-2022
| dominotw wrote:
| right. I get lots of pushback for using sql at my clients.
| They just defeat me with one single point. "where are your
| unit tests" :D
| ericHosick wrote:
| > where are your unit tests
|
| I do unit testing in SQL, and something I'm working on and
| use extensively myself (https://www.npmjs.com/package/sql-
| watch) indirectly supports unit tests.
|
| There are also SQL testing frameworks available.
| taeric wrote:
| I was going to say the same. With the prevalence of
| embedded databases, and how cheap it is to stand up a
| container with non-embedded options, build time testing
| of queries has never been easier.
| dominotw wrote:
| Yea I do that via dbt by setting up a mock data tables in
| database and using a macro to use those as sources/refs
| when run in test mode.
|
| However what we are doing here isn't 'unit testing' its a
| black box integration testing. When I write equivalent
| code in scala, i just test the logic via unit tests,
|
| eg: logic to filter out some orders that don't qualify, I
| extract method in scala code and just test that logic as
| part of development lifecycle. There is no dependency on
| a database.
|
| Analog here is using selenium or some ui testing
| framework to test if button turns blue if order exceeds
| limit. Thats not unit testing.
| ericHosick wrote:
| > I extract method in scala code and just test that logic
| as part of development lifecycle. There is no dependency
| on a database.
|
| Unit testing seems to depend on where the unit of code is
| which is being tested. At the middle tier, you may mock
| out parts of the code so the tests aren't reliant on
| external sources (apis, databases, libraries, etc.).
|
| It seems that unit testing database code would happen at
| the database layer: it's still a unit test as the test
| isn't dependent on external sources.
| dominotw wrote:
| > It seems that unit testing database code would happen
| at the database layer:
|
| Its not database code though. "Orders over 100$ should be
| marked as vip" is domain logic.
|
| Database code like interactions with database, connection
| pools, primary/secondary switching ect yes they should be
| tested with a database.
| Ftuuky wrote:
| Dbt to the rescue!
| vendiddy wrote:
| I think these are great suggestions.
|
| It seems like you're suggesting that someone could design a
| functional-style programming language that compiles to SQL.
|
| 2 & 3 are my biggest pain points. I can't just extract
| functions like I can with a regular programming language.
| Instead, SQL queries get increasingly complex with no great
| tools to manage that.
|
| For 3, products like https://materialize.com/ look interesting
| for being able to create derived materialized views that can
| efficiently be kept up to date.
| eatonphil wrote:
| > It seems like you're suggesting that someone could design a
| functional-style programming language that compiles to SQL.
|
| Not exactly but sort of this:
|
| http://blog.hydromatic.net/2020/02/25/morel-a-functional-
| lan...
| sbuttgereit wrote:
| I'm currently doing work that uses Elixir's Ecto which goes a
| great deal towards what I think you're aiming at. I can write
| my SQL in a familiar, yet functional and composable style;
| while knowing what SQL will be produced in the end. Ecto, as
| I understand it, was inspired by Microsoft's/c# LINQ. I've
| not worked with that, but heard similar praises for that as
| exists with Ecto.
|
| I'm saying this as with most of my experience being in SQL.
| fatherzine wrote:
| "someone could design a functional-style programming language
| that compiles to SQL". See e.g. R dplyr
| coob wrote:
| DBT solves 2 & 3
| alphanumeric0 wrote:
| I'm not familiar with Ecto, dplyr, or DBT, but I would love
| an ML-like language to replace SQL. I'm imagining being able
| to pass a table (or any table-oriented data, like a sub-
| query) to functions that would type-check columns and would
| return table-oriented or scalar data. I'm not sure if this is
| actually possible in practice, but one can dream.
|
| For instance, a "top 10" function that could be re-used on
| any table (apologies for my pseudo types and code):
| selectTop10 : Column -> Table -> Table selectTop10
| orderByColumn table = SQL.selectAllFrom table
| |> orderDescBy orderByColumn |> limit 10
| limit : Int -> Table -> Table limit n rows =
| SQL.limitBy n rows orderDescBy : Column -> Table
| -> Table orderDescBy orderByColumn rows =
| SQL.orderBy [orderByColumn] SQL.Ordering.Desc rows
| chrisjc wrote:
| Not 100% sure about what you're suggesting, but wouldn't it
| be easier to pass your functions to your table/sub-query?
|
| And that's exactly what you're able to do in most of the
| modern data warehouse services such as Snowflake.
| Inferences can be contained within internal/external user
| defined functions.
|
| This is very reminiscent of made the big-data/map-reduce
| movement so notable, sending your query to the data instead
| of moving your data to the query. Sending your model to the
| data, instead of sending the data to the model.
| alphanumeric0 wrote:
| I think I see what you're saying, and in my idea I'm
| suggesting the same - sending the function(s) to the
| data.
| cbm-vic-20 wrote:
| > "we didn't realize how truly awful SQL was or would turn out to
| be (note that it's much worse now than it was then, though it was
| pretty bad right from the outset)."
|
| I wish the "truly awful" stuff I come up with was 0.1% as
| successful as SQL.
| adius wrote:
| PRQL (Pipelined Relational Query Language) to the rescue!
|
| https://prql-lang.org
| Cockbrand wrote:
| While all the criticism is probably correct, and SQL definitely
| shows its age, it's still very much _good enough_ for pretty much
| all its current applications. Also, SQL 's basics are very easy
| to learn. These aspects make it very hard to imagine a language
| that might gain enough traction to actually replace SQL in a
| foreseeable future.
| strbean wrote:
| COBOL also fits this description, down to the awkward attempts
| at matching natural language.
|
| I think the staying power of SQL comes from its broader
| audience - programmers, analysts, and executives all use it.
| It's much easier to motivate programmers to learn a new,
| superior language than it is to motivate executives to learn a
| new technology that only gives ergonomic improvements.
| webmobdev wrote:
| Agreed - my experience with non-SQL tech (ORMs) with Django and
| ASP.net MVC made me really appreciate SQL so much. Most of the
| time I felt everything would be so much easier using raw SQL
| instead of dealing with model objects. It also felt like in
| their quest to replace SQL and make things "simpler" they were
| recreating some db features again.
| SoftTalker wrote:
| Yep. That's why I write most of my logic in stored
| procedures. Working with tables and queries is so much easier
| in PL/pgsql than dealing with ORMs and their leaky
| abstractions.
|
| My application code just calls stored procedures. It's
| unaware of the tables and underlying data model.
| sivers wrote:
| Cool! I do this, but I haven't seen anyone else do it. Is
| any of your code public?
|
| I wrote about it at https://sive.rs/pg
|
| and posted my SQL shopping cart at
| https://github.com/sivers/store
|
| Please contact me if you'd like to share tips:
| https://sive.rs/contact
| cmrdporcupine wrote:
| Many have tried. Most are hobby projects. None have had the
| full force of a real production ready DBMS behind them.
|
| And really, projects like this are fighting the general
| ignorance the industry as a whole has about what a relational
| database actually is. It's better since the NoSQL wave crested
| and we stopped hearing stupid shit like "my data isn't tabular
| and doesn't fit in a schema", but there's still a preponderance
| of people who haven't stopped to learn the lessons that Date
| and Codd tried to teach decades ago, and constantly reach for
| graph/network/hierarchical databases without understanding why
| we originally tried to move beyond those _back in the 70s_.
|
| Unfortunately because of this most attempts to do "better than
| SQL" end up not working from first principles and look like a
| dogs breakfast of ill-formed "that'd be neat" ideas.
|
| And, yeah, as I said the "pure relational" projects that are
| out there are usually either academic projects or hobby. (Yes
| I've had a few of my own)
|
| I think there might be hope in the Datalog-type systems that
| have emerged recently.
| cryptonector wrote:
| Any alternative to SQL has to transpile to SQL in order to
| gain traction.
| cmrdporcupine wrote:
| Which right away rules out a whole bunch of more
| sophisticated and elegant behaviours, honestly.
|
| The other alternative might be to implement one's new thing
| as a patch to alter the frontend of Postgres. I looked at
| this many years ago and the engineering effort was immense.
| But it might be easier now.
| tabtab wrote:
| Many of those complaints seem theoretical. I like to focus on
| practical concerns. The biggest problem I see is that the SQL
| language has grown too complex. It's related to the "Lack of
| Orthogonality" problem mentioned in the article, but I see
| different solutions. SQL is not based on combinations of simpler
| concepts, but hard-coded keywords. But how to orthogonize
| (factor) it gets into philosophical differences. My favorite
| alternative is an experimental language called SMEQL (Structured
| Meta-Enabled Query Language):
|
| https://wiki.c2.com/?TqlRoadmap
|
| It's more API-like to reduce the need for specialized keywords.
| And you can "calculate" column lists via queries instead of have
| to list columns. For example, if a table has 60 columns and you
| want to SELECT all 60 _minus_ two columns, you can 't without
| listing all 58. With SMEQL you can use a query to return a list
| (table) of those 58 and feed it to the equivalent of a SELECT
| clause.
|
| Things like CREATE TABLE are fed a table if you desire so you can
| "compute" your schema. You can generate entire schemas from data
| dictionaries. Anything that can be done with a table is. You can
| create in-line (virtual) tables if you want it command-driven,
| but it's still "using tables to do everything". You can use
| textual code to create such virtual tables or a table editor as
| it fits the situation. SMEQL is as close to "everything is a
| table" as I've seen. Even your FROM list can be table-ized. I
| used to do similar with dBASE/xBASE, and it was really nice,
| especially for ad-hoc work such as one-off research requests.
|
| And as somebody mentioned here, null handling needs a serious
| revisit in SQL. I could rant all day about SQL null handling,
| especially for strings.
| asah wrote:
| I agree!!! this is my pet peeve as well, and I sometimes
| fantasize about ripping into PostgreSQL and adding column-
| minus.
|
| One wrinkle: computed columns would interfere with query
| optimization. That said (and here I speak heresy) there are
| times when syntactic convenience trumps performance.
| cryptonector wrote:
| The syntax could be "* (minus, columns, here)".
| devin-petersohn wrote:
| I always appreciate blog posts like this, there are obviously
| cases where SQL shines, and in part I think the dataframe
| abstraction helps with filling a lot of the missing pieces that
| SQL doesn't handle so well (composability, debuggability,
| interactivity, etc.)
|
| Even pandas (with all its faults) is more flexible as a language
| than SQL[1]. I'm of the opinion that there's a better together
| story in the end, but I guess we will see.
|
| [1] https://ponder.io/pandas-vs-sql-food-court-michelin-style-
| re...
| RyanHamilton wrote:
| qSQL based on the concepts of ordered lists is more appropriate
| for many queries, examples available here:
| https://www.timestored.com/b/kdb-qsql-query-vs-sql/
|
| Kdb the system that qSQL is ran within, allows full use of
| variables and all builtin functions with
| tables/functions/variable/columns. It really is a case of less is
| more. What this allows is functional form queries. Imagine being
| able to query: ?[tableName;lessThan(age;10)] and have perfect
| functional form representation for all queries. No ORM, no string
| concatenation. It seems some other database creators are at least
| becoming area of these things and integrating parts.
| cryptonector wrote:
| I really never want to assume order in tables. That's because
| there could be many orders of interest, and that's one reason
| to have multiple indexes. And also because for a _table_ , the
| order of rows can be hard to guarantee. Obviously something
| like qSQL would try hard to guarantee table row order, and
| that's great in the cases where qSQL is useful, but more
| generally it's going to impose on how you work with the
| database.
|
| On the other hand, SQL is set-based. But being set-based is
| weird in the world of computers because in memory everything is
| ordered. Sets are a fiction in programming, as they're always
| ordered in some way, and the set abstraction can only try to
| _hide_ that order.
|
| And that order can be very useful.
|
| But there can be many orders that can be useful, but only one
| in which things are stored in memory -- the others can only be
| extra indexes.
|
| So the general purpose thing (SQL) has to be set-based,
| offering explicit ordering, and taking advantage of actual
| order for optimization.
| erichocean wrote:
| All SQL database implement the actual Relational Calculus
| internally--it's required to implemented a SQL optimizer.
|
| SQL is just a language for submitting Relational Calculus to the
| database (+ DDL statements).
|
| If you wanted to, for instance, you could add a language
| alongside SQL in Postgres, submit the results to the internal RC
| optimizer, execute the optimized query, and get back the results.
|
| In your new language, you can address all of the issues Carlin
| Eng/Chris Date identify in the article.
| warrenm wrote:
| the title doesn't match the first statement, which states a math
| unfact:
|
| >A Critique of SQL, 40 Years Later 08.11.2022
|
| >The SQL language made its first appearance in 1974, as part of
| IBM's System R database. It is now over 50 years later, and SQL
| is the de facto language for
|
| 1974 isn't 50 years ago yet :)
| adrian_b wrote:
| The title did not refer to years passed since the introduction
| of SQL, but to years passed since the publication of the paper
| with the title "A Critique of the SQL Database Language", i.e.
| from 1984-11.
|
| So the title is correct.
| jakespoon wrote:
| Title may be correct. First sentence is totally wrong. Why
| would I read further?
| cryptonector wrote:
| It's off by _two_ years. People often round 48 to 50. I don
| 't mind TFA rounding 48 to 50; you seem to, so don't read
| it if you don't want to.
| cryptonector wrote:
| If it'd been 1971 and TFA still said "50 years later", would
| you have responded that "1971 is more than 50 years ago"?
| iddan wrote:
| I think we can do much better than SQL without losing its
| inherent power. Projects like Prisma and EdgeDB make me
| optimistic regarding the future of relational querying languages
| jokoon wrote:
| I wish there were SQL "primitives" functions instead of the SQL
| language.
|
| For example if I want to pick a single row by id, with SQL I must
| send a query string, which results in parsing, which means lower
| latency.
|
| If I want to randomly select 100k rows among a database of 1
| million entries, I need to build 10k query strings (I think?),
| which won't be fast to parse. I don't think this happens when
| using C pointers in C, C++ or arrays in other languages.
| zasdffaa wrote:
| If by 'randomly select' you do mean randomly, the TABLESAMPLE
| is what you want (although random means random pages not rows
| and the amount returned may be over or under)
| SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (10
| PERCENT) ;
|
| for example. You can of course specify by rows instead.
| rrrrrrrrrrrryan wrote:
| To get 100k random rows in T-SQL it's:
|
| SELECT TOP (100000) * FROM tblNm ORDER BY newid()
| RedShift1 wrote:
| Isn't that what prepared statements solve? Only parsed on the
| first execution and after that only the parameters change.
| tremon wrote:
| Exactly. Prepared statements or stored procedures avoid the
| continuous re-parsing of identical queries.
| crazygringo wrote:
| I've written a bajillion queries and have tons of nitpicks, but
| it's the twin meanings of NULL that really kills me.
|
| NULL can be the value of a field in a record, but it is _also_
| used to indicate the lack of a record in a JOIN.
|
| If I run: SELECT x.a, y.b FROM x LEFT JOIN y on
| x.a = y.a
|
| and I get back [5, NULL]
|
| I have no way of knowing if that means there's a record [5, NULL]
| in table y, or if there's no record in table y that starts with
| 5.
|
| Obviously there are lots of workarounds, but to me this is a
| truly fundamental design flaw. SQL should have specified another
| NULL-like value, call it EMPTY, used only for joins that find no
| data. And analagous to IS NULL, it would be checked using IS
| EMPTY.
| janci wrote:
| Also you should select y.a to know wether the y-record exists.
| onlyrealcuzzo wrote:
| How do you expect `y.b` to behave on EMPTY rows? Should it
| return EMPTY or NULL?
| rrrrrrrrrrrryan wrote:
| Why are you joining on a nullable column in the first place?
|
| If your database is well designed, joining on a nullable column
| should be a relatively exotic use case, and can be handled by
| writing a tiny bit more code to check for NULLs before
| performing your join.
| crazygringo wrote:
| It's not.
|
| Assume y.a is the non-nullable primary key, while y.b is the
| column that may be null.
|
| But in this example there might not be a row where y.a = 5.
| Or there might be. But you can't tell.
| tremon wrote:
| But in those cases, you can always tell by including the
| non-nullable key in your result set. It's already being
| evaluated, so it's virtually free (only adds to the network
| transport size).
| thedataslinger wrote:
| To piggy-back, it bothers me so much that this is valid syntax
| in many implementations:
|
| UPDATE x SET a = NULL WHERE b IS NULL;
|
| Like... wat?
| cryptonector wrote:
| You need one more output column. The specifics will vary by
| RDBMS because SQL isn't _that_ standard. The most portable
| thing would be to do this: SELECT x.a, y.a IS
| NOT NULL, y.b FROM x LEFT JOIN y ON x.a = y.a;
|
| assuming y.a is a NOT NULL column, or a PRIMARY KEY column,
| since PKs are supposed to be non-nullable.
|
| In PG you could: SELECT x.a, y IS DISTINCT FROM
| NULL, y.b FROM x LEFT JOIN y ON x.a = y.a;
| goto11 wrote:
| A nullable column can always be extracted to a separate table
| with a non-nullable column and a foreign key. If you left-join
| this back with the base table, you will get the nulls again for
| the missing values. So to me it seem nicely symmetrical to use
| the same kind of NULL value.
| gfody wrote:
| you should not need to distinguish between empty and null
| because they mean the same thing. if you want to select records
| from x that aren't in y you should use an anti-join (where not
| exists, not in, etc.)
| thedataslinger wrote:
| This is not true--especially when considering different SQL
| implementations (e.g. Oracle SQL versus Microsoft SQL). NULL
| and EMPTY handle the intersection of ontic versus epistemic
| claims.
|
| EMPTY implies a known, 0-byte value whereas NULL can imply
| either an unknown value or the "unknowability" of a value
| (i.e. the in-existence of a value).
|
| In practical terms, this would be like equating the
| statements "I don't know whether that dog has a name" (i.e. a
| NULL name) and "I do not know the name of that dog" (an EMPTY
| name). The former does not assert the existence of a proper
| noun to represent "that dog", whereas the latter implicitly
| asserts that there exists a proper noun which nominates "that
| dog".
| gfody wrote:
| beg pardon I wasn't aware sql actually defined empty. of
| the engines I'm familiar with there is no keyword for empty
| like what gp is asking for (and I hold my argument for why
| it should not be necessary). it sounds like what you're
| describing is a blank string and I agree with using blanks
| to distinguish between "has no phone number" and "we don't
| know their phone number" but that isn't what gp was talking
| about.
| dspillett wrote:
| _> but it 's the twin meanings of NULL that really kills me_
|
| NULL only has one meaning: NULL. This is roughly analogous to
| unknown.
|
| The one that his a lot of people is WHERE <value> NOT IN
| (<set>) where <set> contains a NULL. Because NOT IN unrolls to
| "<value> <> <s1> AND <value> <> <s2> AND ... AND <value> <>
| <sx>" any NULL values in the set makes one predicate NULL which
| makes the whole expression NULL even if one or more of the
| other values match.
|
| _> I have no way of knowing if that means there 's a record
| [5, NULL] in table y, or if there's no record in table y that
| starts with 5._
|
| Not directly, but you can infer the difference going by the
| value of (in your example) y.a - if it is NULL then there was
| no match, otherwise a NULL for y.b is a NULL from the source
| not an indication of no match.
|
| _> SQL should have specified another NULL-like value_
|
| This sort of thing causes problems of its own. Are the unknowns
| equivalent? Where are they relevant? How do they affect each
| other? Do you need more to cover other edge cases? I have
| memories of VB6's four constants of the apocalypse (null,
| empty, missing, nothing).
|
| This is one of the reasons some purists argue against NULL
| existing in SQL _at all_ , rather than needing a family of
| NULL-a-likes.
| onlyrealcuzzo wrote:
| > This is one of the reasons some purists argue against NULL
| existing in SQL at all, rather than needing a family of NULL-
| a-likes.
|
| So then are there no optional values? What happens when OUTER
| JOINs don't match?
| vbezhenar wrote:
| You don't do outer joins.
| Koshkin wrote:
| Don't these exist for a reason?
| andy81 wrote:
| Of course.
|
| You've got a list of countries and are pulling each
| country's national flower, national bird, largest port
| city etc.
|
| Without outer joins, Liechtenstein with no ports doesn't
| show in the list at all. Sad news for people who want to
| know all countries, or Liechtenstein's national bird
| (eagle).
| vbezhenar wrote:
| You're not obliged to pull everything in one request. You
| can issue several requests.
| tremon wrote:
| Sure. You're also not obligated to include a WHERE-clause
| in the query you send to your database. You can do the
| filtering in the application.
| vbezhenar wrote:
| What's your point? You can live without nulls, they're
| not required and you don't need to avoid WHERE-clause for
| that. It's a theoretical concept, nobody in sane mind
| would do that, but nulls are not required for relational
| algebra.
| dspillett wrote:
| I assume NULL would still exist there, but there would be
| no explicit NULL values permitted in tuples (rows) that
| actually exist.
|
| Or perhaps the purists would remove outer joins too, it
| isn't since University that I've read around the
| discussion, but given alternate syntax to do the same thing
| can sometimes be convoluted that might be a bad idea
| itself.
| chasil wrote:
| NULL itself is open to interpretation.
|
| If I CREATE TABLE foo (bar CHAR(1) PRIMARY KEY, baz char(1)
| UNIQUE), then different things happen on different databases.
|
| In Microsoft SQL server, only one insert of a null into the
| baz column is allowed, and the null value is indexed.
|
| In Oracle, null is never indexed in this context, so any
| number of null insertions into baz are allowed.
|
| On a composite index, I believe that nulls are always
| indexed, on any database (they must be).
|
| As far as I understand it, this is implementation-defined:
| SQL> select * from dual where null=null; no rows
| selected
| crazygringo wrote:
| > _NULL only has one meaning: NULL. This is roughly analogous
| to unknown._
|
| Which is what I'm arguing against, because it's used in two
| unrelated ways in SQL -- as a data value, and to express no
| matching row found in a JOIN. So no matter how it's defined
| formally, _in practice_ it has _two_ meanings that have
| nothing whatsoever to do with each other. One is a value and
| can be stored, the other says 'not found' and results only
| from expressions and isn't for storage.
|
| > _but you can infer the difference_
|
| Yes, as I said there are lots of workarounds. But they're
| still workarounds.
|
| > _This is one of the reasons some purists argue against NULL
| existing in SQL at all_
|
| Sure, but the reality is that NULL is used in a practical
| sense to mean "no data entered". It's so ridiculously common
| that only some columns have no data for a particular row, and
| so e.g. you need the 'time_finished' column to either have a
| valid date, or it's NULL if the activity hasn't finished yet.
| The alternative is to have an additional boolean column
| 'is_finished' and for 'time_finished' to be arbitrarily
| '1970-01-01T00:00:00Z' whenever is_finished is false, which
| is clunky and redundant.
|
| Purists can argue what they want, but NULL is so ridiculously
| useful as a stored data value it doesn't really matter.
| yen223 wrote:
| Javascript having both `null` and `undefined` doesn't seem
| so crazy now
| jon_richards wrote:
| > you need the 'time_finished' column to either have a
| valid date, or it's NULL if the activity hasn't finished
| yet
|
| Rust's Diesel has an interesting way of dealing with this.
| "None" means missing while "Some(None)" means the value
| null. So when updating a record, "None" makes no change
| while "Some(None)" sets the value to null.
|
| This distinction is obviously lost when _retrieving_ from
| the database, but it's an interesting concept.
| SoftTalker wrote:
| If you have a business need to represent "empty" or "n/a" or
| "declined to answer" or something like that, use a specific
| value for that. NULL does not mean anything. Or, it means
| nothing. It's just NULL. Once I got that into my head, SQL
| became less frustrating.
| andy81 wrote:
| A better option in many cases is to check the primary key.
|
| e.g.
|
| select questions.Id , questions.Text , answers.Id ,
| answers.Text
|
| from questions left join answers on answers.QuestionId =
| Questions.Id
|
| answers.Id is non-nullable as a primary key, so if
| answers.Text is null but answers.Id is not null they've
| declined to answer.
| dspillett wrote:
| That may be implementation or circumstance specific. For
| instance in MS SQL Server with a heap table (one without
| a clustered index) or a table where the primary key is
| not the clustering key, it will result in extra page
| reads to check the other field's value (the query planner
| / engine could infer from it being the PK that it can
| never be null, so the lookup to check is unnecessary, but
| IIRC it does not do this). As the columns used in the
| join predicate have to be read to perform the join, no
| extra reads will result from using them for other
| filtering.
|
| In your example it is very likely that the primary key is
| the clustering key, so will be present in the non-
| clustered index that I assume will be on
| answers.questionId, making my point moot, but if for some
| unusual reason neither Id nor questionId were the
| clustering key checking Id may result in extra reads
| being needed.
|
| In DBMSs without clustering keys implemented similarly to
| SQL Server, there may be such concerns in all cases.
| Koshkin wrote:
| Indeed, the issue of _non-existence_ is a somewhat tricky
| philosophical question (e.g. there are many ways something
| may not exist).
| crazygringo wrote:
| You can't without defining yet another field, usually. It's
| really annoying to double the number of columns so that
| [time_started, time_finished] becomes [time_started_exists,
| time_started, time_finished_exists, time_finished].
|
| NULL values makes business logic far more compact and
| intuitive. For enumerated values in fields it's easy enough
| to define another value in the same field to mean 'unknown'
| or 'not entered', but you can't do that for strings,
| numbers, datetimes, etc. -- you have to throw in a bunch of
| unwieldy additional boolean fields instead.
| Quekid5 wrote:
| The underlying problem here is that SQL lacks Sum Types
| (aka Tagged Unions). Such types solve all these problems
| effortlessly.
|
| In contrast to what SQL has, Sum Types combined with
| Product Types (which is basically what a row is) are
| actually a universal way to model all possible data[0].
| (Of course you may want syntax sugar, etc. on top of
| that, but Sums and Products at the bottom is sufficient.)
|
| [0] I'm actually not sure if I should qualify that -- I
| believe Sums+Products can actually model anything,
| assuming you allow recursive type definitions -- which
| might be hard to make perform well. Storing a linked list
| in a database field, e.g. might not be the best idea.
| paulclinger wrote:
| > I have no way of knowing if that means there's a record [5,
| NULL] in table y, or if there's no record in table y that
| starts with 5.
|
| You can always add SELECT y.a, which will allow you to
| disambiguate between the two options (it will be non-NULL in
| the first case and NULL in the second).
| Shorel wrote:
| Good point, it would be nice to have both NULL and EMPTY to
| split these meanings.
| janci wrote:
| This is why JS has both null and undefined.
| tremon wrote:
| Ah, JS, the pinnacle of data correctness. 1 == "1" results in
| undefined, right?
| merb wrote:
| nope 1 == "1" is true and 1 === "1" is false
| ianmcgowan wrote:
| I'd check to see if y.a IS NULL in that situation. I'm sure
| there are cases where it matters, but most of the time for me
| the difference between "there's a row in y, but the value is
| NULL" and "there's no row in y, the value is NULL" is
| irrelevant. I can't think of a time when that distinction has
| been important, and I'm working on a project converting
| thousands of complex SQL queries.
|
| The thing that really bugs me about NULL is the default
| assumptions - 99.9% of the time I want NULL in a string context
| to be the empty string and NULL in a numeric context to be 0,
| but I have to use ISNULL or COALESCE to get that. I wish it
| were the other way round where NULL is treated as '' or 0 by
| default, but I can do something special if I really want to
| test for NULL'ness.
| crazygringo wrote:
| Interesting, we must work with really different data/queries.
|
| I've come up against the distinction lots of times (and yes
| have to retrieve additional fields in order to address it),
| while I don't ever want to confuse 0 with NULL. _Tons_ of
| things are legitimately zero but crucially non-null, like an
| inventory count. (Empty strings, on the other hand, do seem
| much more interchangeable with NULL in probably the vast
| majority of contexts.)
___________________________________________________________________
(page generated 2022-08-24 23:01 UTC)