SQL Join Types (1 Viewer)

[Pat Hartman]

I built a teaching database for a class I'm giving and I thought I'd share a part of it with you. This is a report showing all the join types with SQL and a Venn diagram to help you to understand what the join is really doing.
Let me know if you like the presentation. I included a pdf so you can print it easily and keep it handy. Hang it on the wall behind your desk Also, thanks to @Uncle Gizmo for the idea to create the report as a summary to make it easier to compare the SQL

 

[Galaxiom]

Nice.

Maybe mention the name variations too?
Left Join and Right Join are types of Outer Joins. Left Outer Join, Right Outer Join.
Similarly, Inner Join can be written as just Join.

Cross Join is also known as a Cartesian Join or Cartesian Product though not written as such in the SQL.

The SQL column would be be better formatted with the key words starting each line.

 

[CJ_London]

Might want to include non-equi joins as well. In Access you can use >,<,>=,<=, in SQL server there are more (Between for example)

 

[arnelgp]

Excluding has Wrong Concept.
sql - Left-join EXCLUDING matching records? - Stack Overflow
what you have is but an Ordinary Filtered query.

 

[Pat Hartman]

The Left and right Excluding do exclude matching records. Keep in mind that Access supports a 40 year old version of SQL. That's why the Full Outer Join is done with a Union.

 

[isladogs]

Agree with earlier comments by both @Galaxiom & @CJ_London

Perhaps also consider adding examples using EXISTS & NOT EXISTS for your exclusionary joins.
Plus UNION ALL
And for completeness, don't forget AMBIGUOUS joins

 

[Pat Hartman]

What is an ambiguous join?

The point of the exercise is to help people to understand the data that will be selected in a visual manner. Hence the Venn diagrams. It is not a complete description of the options available beyond a basic join. So non-equi joins and Union All are for a different lesson.

 

[plog]

Also, you completely forgot about GROUP BY and DISTINCT and PIVOT and TOP and aliases and the difference between 0 and NULL and self joins and cojoined twins and case sensitivity.

 

[Pat Hartman]

You miss the point of the example. It also doesn't talk about the price of tea in China.

 

[isladogs]

What is an ambiguous join?

A query with 3 or more tables where the joins are in opposing directions so Access doesn't know how to handle the query

I suggested including an example so end users know how to avoid it or find a work-round

 

[Pat Hartman]

Thanks. Thought there was a new join type out there

As I said, this is the simplest example I could make that would VISUALLY describe what each join type does. It is NOT a complete explanation of query options or the SQL used to make queries. The SQL is DELIBERATELY as simple as I could make it. It depicts the ESSANCE of a query, not all the possible options. This is a presentation for rank beginners. I didn't even post the tables because you can envision them from the queries. Maybe you're all too jaded to say "wow, that's a cool way to show what data is being selected by a join. I've never seen that before" which is exactly what Uncle said when I showed it to him. No one even commented on that. This is a SUMMARY with a VISUAL that is ONE PAGE that a novice can easily refer to if he is confused about what join he needs. It is not an entire course.

 

[isladogs]

Sorry Pat but I'd seen and commented on the diagram before as you have posted it previously.

Yes - it is a very good visual guide.
The suggestions were intended to make it even better whilst still being aimed at novice users

 

[Pat Hartman]

I never posted it before since I just made it two days ago and I've never posted the teaching database publicly (although I might have sent it privately).

I know you all mean well but no one understood the intent of the example, nor the need for brevity. Maybe I should have explained its intended usage better.

 

[isladogs]

Either you sent it privately or perhaps I've just seen something very similar before

 

[Uncle Gizmo]

D

wow, that's a cool way to show what data is being selected by a join. I've never seen that before" which is exactly what Uncle said when I showed it to him

I did at that...

I think it's perfect Pat, very clever and very well done. I can't wait until you release the app that demos the queries.

 

[isladogs]

Pat
This is where I'd seen it before...

But perhaps I was also thinking of one of the many other Venn diagrams for this topic I've seen online. For example:

There are many examples because its a good way of visualising what the joins mean

 

[jdraw]

I remember posting this png a few years back.

 

[Gasman]

And apparently, this another way?

You Should Use This to Visualize SQL Joins Instead of Venn Diagrams | Towards Data Science

Venn diagrams are so last year

towardsdatascience.com

NB: I personally am not saying Venn diagrams are so last year.

 

[isladogs]

And apparently, this another way?

You Should Use This to Visualize SQL Joins Instead of Venn Diagrams | Towards Data Science

Venn diagrams are so last year

towardsdatascience.com

NB: I personally am not saying Venn diagrams are so last year.

Interesting....never thought of doing it like that!

Or you can just use no diagrams at all...as in my article

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

 

[Uncle Gizmo]

I built a teaching database for a class I'm giving and I thought I'd share a part of it with you.

I think you all missed this. Pat has created a very nice SQL joins teaching database.

The PDF is just a taster of it. The PDF, derived at my suggestion from the forms there in, so that the student can easily compare the various SQL statements shown in the PDF.

The database runs each of the SQL statements as shown on that PDF. You can see the results and compare the result to the base tables, giving the student insight in how SQL works.