3 ms·

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' tabl

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.

danso 10y ago
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).
- ZenoArrow 10y ago
  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.

danso 10y ago

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

ZenoArrow 10y ago
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.