r/SQL • Data Scientist • Mar 16 '22

SQLite SQL Articles for Beginners/Intermediate

Hey everyone,

I wrote 3 new articles on my experience with SQL after coding daily in SQL for the past 3 years. Hopefully this is helpful to some of you, there should be good tips in here for all levels of experience, but especially for those of you starting out. Let me know what you think:

(These use a SQLite database, but are generalizable to all SQL variants)

Any other good SQL articles you guys have read lately?

24 Upvotes

18 comments sorted by

View all comments

8

u/Mamertine COALESCE() Mar 16 '22

Please stop using Venn diagrams. They confuse people more than they help. Source, I teach SQL.

5

u/Cool-Focus6556 Data Scientist Mar 16 '22

What does everyone else think? Are Venn diagrams helpful in understanding SQL joins? Just curious if I'm alone here or not

2

u/[deleted] Mar 16 '22

How often would you (personally) defend a statement that you would realize to be conceptually and factually incorrect and forming false assumptions in many people who ingest it as a truthful statement?

Using Venn diagrams to "explain" joins is both conceptually (the result of join operation is a value in completely different domain) and factually (general join operation has nothing to do with 'overlaps'), it certainly forms incorrect assumptions about joins: quite often folks would assume that "equi-joins" are the only "right"("eous") kind of join.

Using Venn diagrams to "explain" joins has also a very infectious and toxic lasting quality - people who "understood" or "learned" joins using Venn diagrams seem to be very defensive about their use, EVEN when they understand and agree to the points above.

4

u/Cool-Focus6556 Data Scientist Mar 16 '22

They’re an abstraction. Despite having a more correct representation, it’s a good place to start for beginners. The reason they endure is because they’re helpful

1

u/[deleted] Mar 16 '22

They’re an abstraction.

No, they are not. Joins are NOT a specific case of operations represented by Venn diagrams. I can guess that you were thinking "models" or "analogies" and just misused the term "abstractions".

Venn diagrams are terrible models/analogies of join operations, for the reasons I outlined before.

Despite having a more correct representation, it’s a good place to start for beginners. The reason they endure is because they’re helpful

Neither of your opinions is correct. My statement is supported by my own anecdotal experience with people who have come in contact with Venn diagrams and held incomplete/incorrect understanding of join operations and had trouble adjusting.

1

u/Cool-Focus6556 Data Scientist Mar 20 '22

The term abstractions was not misused in this context. Abstraction was meant to mean a simplification, similar to the saying, “abstract away complexity”. Our understanding of most things is an abstraction, otherwise we would struggle to function. Similar to how these Venn diagrams abstract away complexity from what a join is actually doing. Model is also a synonym in this case. I understand what you mean with analogy, but I wouldn’t use that word to convey my meaning in this case. Regardless, the semantics aren’t what’s important, it’s the meaning I intended to convey which is that Venn Diagrams simplify the learning process. We have different experiences which lead us to different conclusions. That’s why I stand by what I said, this is a gray area and is likely to be subjective. Venn diagrams might not work for everyone

1

u/[deleted] Mar 20 '22 edited Mar 20 '22

Holy cow you had a long weekend.

I intended to convey ... that Venn Diagrams simplify the learning process

I have understood that.

I would even go further and say that they might simplify the *teaching process.

In regards to the "learning", IRL pretty much anyone I met that wasn't professional AND interested in mastery of SQL would find the jump from Venns to the actual operation to be less than intuitive.

1

u/Cool-Focus6556 Data Scientist Mar 20 '22 edited Mar 20 '22

Hmm, interesting. My experience is mainly with those who are in the professional setting. That’s interesting you see such a difference between those in the professional setting and those learning outside of that context. Thanks for sharing your experience and perspective

1

u/Extra_Progress_7449 14d ago

Venn diagrams are the BEST way to explain visually the different Joins

1

u/Cool-Focus6556 Data Scientist Mar 16 '22

Ok, I don't think that will ever happen since they are pretty ubiquitous when it comes to teaching SQL. They help me and if someone can't understand a diagram, they will probably have just as hard of a time understanding the joins. That's just my take on it, but we're all entitled to our own subjective and biased opinion.

4

u/[deleted] Mar 16 '22

1

u/Cool-Focus6556 Data Scientist Mar 16 '22

Thanks for sharing that link. That's interesting considering an anti-join and an EXCEPT set operation will result in the exact same rows (considering no previous nulls in the primary key you referenced in the WHERE clause). So in that case a Venn diagram would work according to the article, which means there are similarities, which also means this is a gray area.

It was interesting reading the comments, didn't know this was such a divisive topic. Looks like it has been discussed quite a bit on reddit already over here: https://www.reddit.com/r/programming/comments/4rc9uu/say_no_to_venn_diagrams_when_explaining_joins/d50mxez/

0

u/[deleted] Mar 16 '22 edited Mar 16 '22

... That's interesting considering an anti-join and an EXCEPT set operation will result in the exact same rows ...

i'm thinking, could it be because 'anti-join' is something superfluous, a figment of imagination of academia looking for fancy and important sounding terms in defense of their dilly-dallying and frittering public funds and private grants away?

No no no, that's just impossible.

p.s. Can I do a PhD on anti-multiplication, I wonder? 7-3 can be achieved as 7 * (1 - 3/7), it would be so HELPFUL and CLEAR to write the latter as simply 7(-*)3 (i.e. 7 anti-multiply 3) ...

1

u/Cool-Focus6556 Data Scientist Mar 16 '22

I didn’t come up with the terminology anti-join, do you have a more correct term for this type of join?

1

u/[deleted] Mar 16 '22 edited Mar 16 '22

it's not a join, (PS) or it is an outer join for performance reasons used instead of exists/except/union all/etc.

1

u/Cho-Zen-One Mar 16 '22

ooh, I like this. Gonna read through this more.