Comment by danso | Hacker News Reader

Comment by danso | Hacker News Reader

Parent Full thread

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.

> 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.

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.

What will the id become in that query when you got multiple people with the name? Null?

The solution is very simple. If when you're starting out you don't understand that the 'details' table should be your primary table instead of the 'header' table, then there's a simple trick you can pull... For any inner/left join you create, try altering the query to use a right join and check you don't return more results. That way you can be sure you're targeting the appropriate table.

I'd also recommend using LEFT JOIN over INNER JOIN when starting out exploring data, as the rows with null values can be a useful prompt to check your query logic.

Also, as another SQL tip... always use table aliases. Not only do table aliases make it clear which table you're pulling from, they also help you fill in the right fields if you're using a decent editor that has IntelliSense or something similar.

There's very little reason not to use table aliases, especially if your query is pulling from more than one table.

So the query becomes...

SELECT r., i. FROM restaurants r LEFT JOIN inspections i ON r.name = i.restaurant_name

...and when checking results, just swap LEFT for RIGHT...

SELECT r., i. FROM restaurants r RIGHT JOIN inspections i ON r.name = i.restaurant_name

This last step is unnecessary when you know to target the 'detail' table in the FROM clause.

EDIT: The HN comment system is removing the asterisks after r. and i. . Would just need to add those back in for the queries to work.

I like that idea of teaching LEFT JOINs before INNER JOINs...I've always done the opposite because that's how I learned it (via some book about Microsoft Access) and maybe because INNER JOIN is the default kind of join if you use only "JOIN".

I teach table aliases but only out of necessity when doing nested queries...they aren't hard to grasp but I've assumed it to be less of a priority when students are still struggling with vanilla SELECT/FROM/WHERE. (Also, I learned aliases somewhat late...they didn't seem useful until working in a production environment...just as proper naming conventions in any language seem like trivia until you have to work with someone else).

Glad you like the LEFT JOIN idea. Thank you for being open to new ideas.

Regarding table aliases, I understand the reluctance to overload your students with too much information, but in my experience they really do make writing SQL a lot easier (when coupled with an IntelliSense-capable environment such as SSMS).

Every time a student wants to reference a specific field they type one or two letters (ideally a name that is easy to remember, so if there was an ObjectType table a good alias would be OT, for example), a full stop, then all the available fields from that table are shown in a list. Nothing else is in that list, which makes the decision simpler to make. In SSMS, the IntelliSense usually displays automatically. Sometimes you experience a brief delay from building the cache, but manually toggling IntelliSense usually gets around this (it can be toggled manually with Ctrl + Space, which is an easy shortcut to remember).

On a side note, I tend to use the AS keyword when creating table aliases as I like making them explicit, perhaps that habit will also be beneficial to your students as it's the same keyword used for renaming fields in the SELECT clause also, so in a student's head they can think of AS as rename.

Reply on news.ycombinator.com