3 ms·

I would agree except for that once you start adding joins into the mix people get easily confused, while you can certainly express the concept in english (and w

• danso 10y ago
  I agree that JOINS can be complicated, even at their most basic. One very common error is doing a JOIN on what is assumed to be a unique foreign key: SELECT * FROM restaurants INNER JOIN inspections ON inspections.restaurant_name = restaurants.name (Ideally, `inspections` would not have `restaurant_name` as a field...but we're talking about legacy government data, in which anything could happen...) In the best case scenario, the user realizes that the result contains more rows than in `inspections` and, furthermore, realizes that that's an illogical result because (again, ideally) the user realizes that `inspections` has a many-to-one relationship to `restaurants`, and then realizes that `name` shouldn't be used as the foreign key. The worst case scenario is that the user understands none of those concepts -- the need for foreign keys (in this case) to be unique, what "many-to-one" is -- and also is ignorant of the domain -- e.g. that there may be more than one restaurant with the name "MCDONALDS". And then throw in a few data integrity errors, e.g. `inspections.restaurant_name` contains typos/mistakes/nulls, such that the number of rows in the result set is fewer than number of rows in `inspections`. But this worst-case user is just satisfied/relieved that the SQL interpreter didn't throw an error and goes on to conduct further erroneous analytical queries on this nonsensical result. But none of that is the fault of SQL itself. And I have hard time thinking of how exactly SQL, or any programming construct, can make such logical (but not syntactical) errors evident to the novice. At least SQL makes this error fairly evident to subsequent readers of the code.
  • dragonwriter 10y ago
    > And I have hard time thinking of how exactly SQL, or any programming construct, can make such logical (but not syntactical) errors evident to the novice. SQL could do one (or both) of these to make that easier: (1) have a preferred syntax for joins that leveraged declared relationship (FK) that required the referenced column(s) of the FK relationship to be a declared candidate key (either PK or subject to a Unique constraint) -- which would make other joins automatically suspect. EDIT: Incidentally, I've seen proprietary RDBMS languages and visual interfaces (usually, for systems that also supported SQL) that did this exclusively -- you could only join via declared FK relations. This is safer, but probably overly restrictive for general use. (2) in something otherwise like the existing join syntax, require an explicit modifier to do a join without a constrained-as-unique column on one side.
    • danso 10y ago
      Ah, I hadn't thought about teaching the novice user to declare their own foreign keys (assuming that the data workflow starts with importing a plaintext CSV into a database) before doing a JOIN. Such a process would force the user to think about what a foreign key column should contain. (in the past, I've generally avoided teaching table/data altering statements, such as CREATE/DROP/UPDATE, until after thoroughly covering SELECT) I use SQLite exclusively for teaching, but one thing I miss from PostgreSQL (in my limited usage of it, at least), is how it throws an error when the user attempts to select columns not referenced in a GROUP BY clause: SELECT id, last_name, COUNT(1) AS people_with_that_name FROM people GROUP BY last_name; `id` is the problematic column here. But in SQLite and MySQL, the interpreter will dutifully include the column even though it has no meaningful interpretation. Come to think of it, SQLite's simplified data types (including implicit type casting) is kind of a pain when it comes to teaching.
      • hjalle 10y ago
        What will the id become in that query when you got multiple people with the name? Null?
  • ZenoArrow 10y ago