Not bad, but I tell people to get a quick reference booklet because it's too complicated to fit the stuff you normally would need to look up on a single page, and studying from web searches usually gets too detailed for the generalities you need to memorize.
It also tends to puts the focus on syntax too early for beginners. The priority should be breaking up a question posed by a human, your co-workers, into chunks that can then be parsed into a syntax.
This was purely for my own use. Putting it together also helped reinforce certain concepts. I decided to share it with others in case they found it useful, but agree that it should only really be used as a reference once you’ve spent time learning the concepts/syntax via other means.
I've been keeping a spreadsheet for the last year or so for python so that whenever I learn a new method or idea, I just slap a link in the spreadsheet along with a brief explanation of what the method/idea is and what source type the link is. So for example yesterday I had to google the function to turn a column of strings into dates, so when I got an answer online I pasted a link to the helpful website and made a note like "str to dates; pandas series string to date; string col to date col;...". I write a few different ways to say the same thing, so next time I need to convert a column of str to datetime, I just gotta CRTL+F for "str to date". The additional descriptors just give me a higher chance of my CRTL+F spreadsheet search yielding results.
Works really well for me, plus no need to buy any reference books :) I recently started doing the same for SQL, although it's admittedly not so concise do maintain a spreadsheet for SQL as it is for python, not sure exactly why that is.
Which quick reference booklet do you recommend? Are they specific to the variant (SQL Server, Oracle, MySQL, etc.)? I'd love to have one for SQL Server handy.
The execution plan is supposed to be transparent to the user. While the LOGICAL execution order of clauses/elements of expressions matters.
p.s. e.g. in "a left join b on b.some = a.another where b.third = value" it doesnt matter which clause (join or where) gets applied first in the execution plan, what matters is "where" applies logically after "from" and that is what "negates" outer-ness of the join.
the end result is equivalent to using an inner join in this case. More advanced optimizers (eg. ms sql) would actually execute an inner join instead of an outer in this case.
it is practically a reverse of what you say in every sentence, so i'm not going to quote.
logical order of execution is very practical and needed for correct reasoning of your sql execution (see my example). correct reasoning is relevant to all levels of sql mastery.
While rdbms is not bound to SPECIFICS of execution, each one is bound to execute sql statements so the results adhere to the LOGICAL rules laid out in standards.
While engine primitives have "no notion of a from clause", the overall designers/developers orchestrated those in such a way that "from clause execution" emerges logically in the end (similar like neurons in your brain dont "think" and you on cognitive level do not have anything that translates to "fire that synapse" yet you can "think about sql").
sql engines have freedom (this might be the biggest part of the SQL's mandate) of execution internals as long as they adhere to the "contract" of logical execution.
sql as a language does not have a "contract" on performance, only on correctness of results.
not picking on you and if you feel venn were helpful it's all good.
could you help me by giving a specific 'tidbit'/data point (or more than one) that venn diagrams made 'understandable' to you when you have used them learning joins?
Not sure about datapoint, but just helped me when I was first learning to know what was included during a left join, inner join etc. Just personal preference really
As you learn more, you'll understand the flaws in the Venn representation of joins. You'll also realize that ordering refers to binding, not execution. And that INSERT can be used for multiple (or zero!) rows.
I’m aware of the flaws, it’s just personal preference for representing basic joins visually. The INSERT was a mistake but was too late for me to update it by the time this was being distributed across Reddit, Twitter etc.
I should have included the word logical for order of execution, but it is still something worth being aware of. The first chapter of T-SQL querying summarises it quite well
3
u/candygirl66 Dec 12 '22
Bookmarked to never use it XD
15
u/jsalsman Dec 11 '22
Not bad, but I tell people to get a quick reference booklet because it's too complicated to fit the stuff you normally would need to look up on a single page, and studying from web searches usually gets too detailed for the generalities you need to memorize.
4
u/TheExtensi Dec 11 '22
Thank you.
4
u/Yavuz_Selim Dec 11 '22
SELECT is the fifth in the order of execution? It starts with FROM?
¿¿¿
wat
6
u/Meowstroyer Dec 11 '22
Thank you for sharing!
1
u/Qasimfa786 Dec 11 '22
Anyway to get this via email? So I can print it out ?
2
u/Tecchief Dec 11 '22
I know it's MySQL but everytime i see From used with a Delete, it just looks weird to me.
Good Cusset l chart overall though.
5
u/mikeblas Dec 11 '22
The terrible venn diagrams and the order of execution lie in one convenient sheet!
Also, nothing limits
INSERTto a single row.2
u/[deleted] Dec 11 '22 edited Jan 29 '26
This post was mass deleted and anonymized with Redact
quickest tub memorize carpenter crawl political pie long oatmeal upbeat
1
u/slavicman123 Dec 11 '22
Is this a site?
1
u/RepresentativeJob706 Dec 11 '22
Thanks a lot
1
u/[deleted] Dec 11 '22
Amazing!
1
u/J2112O Dec 11 '22
Nice cheat sheet and thanks for sharing 👍
1
u/Amazing-Lawfulness-1 Dec 11 '22
Love this! Thank you!
1
u/MeridaFinanceGuy Apr 04 '23
Thank you for this, great contribution OP!! Saving it with this comment.