My first SQL introduction was the classic select * from tableName; in a production environment with a read-only user, with goosebumps as the screen was filled with data. My next shivering was followed shortly afterward with filtering using the with-clause, and the feeling of superpowers when I learned that I could list the tables before the with and link them in this way table1.id = table2.id
I have been able to both teach and watch several colleagues learn SQL for the first time and see them develop their skills to more advanced levels. What astounds me is how most courseware and people learn the join-keyword late, after a lot of pain with null-values and incomplete results. There are several ways you can avoid the need for the different versions of join, and as you explore the language you learn with trial-and-error your way to find these tricks.
xIt took me a while to understand that with could just be replaced by on, and some other things to remember, as long as you also replaced the comma between the tables with join.
Once in a while I like to retake some courses and fast read some books on topics I already know, because I often find new tricks I didn’t know before. Today I took the KhanAcademy course on SQL and the site is developed in a way that it try to run your code as you write it, no hitting run as you do on CodeAcademy.
I had my original code including the with-clause and implicit joint (select * from table1, table2 where table1.id = table2.id) that I was about to turn into an explicit join (select * from table1 join table2 on table1.id = table2.id). As soon as I switched from with to on the code still worked. Hmm.. That was strange. Lets try something else. That still works. A bug in the KhanAcademy platform?
Tried to do the same in the Oracle-environment and an error message as expected
Had to check out the sqlite3 also, and it worked..
Wonder how the syntax for sqlite3 is looking
And going into the join-clause of the image
That was strange.. This is not how it worked for me in sqlite3. It was more this way:
Where the join-constrain is the on, without any join-keyword:
Is this a bug or a feature in sqlite3? I like the possibility to switch from using implicit join and with, into something more like “semi-explicit join” without the need for the join-keyword.
— —
Update: Thanks to advisedwang on HackerNews to point out that the JOIN-operator is there through the comma between the tables.
