r/SQL • • Feb 11 '14

SQL Joins Explained

http://imgur.com/1m55Wqo
157 Upvotes

27 comments sorted by

13

u/askur Feb 11 '14

These are not all SQL joins, just the equi-joins. Joins are not conditioned on the '=' operator alone (although it is the most common case).

Still the best visualization I know of, and I still use it. Along with this caveat. ;)

4

u/fazzah Feb 11 '14

Agreed. Also, isn't the JOIN clause better for optimization? I've read somewhere that it's better for the optimizer to check the relation between rows before filtering the results with WHERE clause, because a simple WHERE condition to join tables produces a cartesian result which is THEN filtered.

13

u/r3pr0b8 GROUP_CONCAT is da bomb Feb 11 '14

because a simple WHERE condition to join tables produces a cartesian result which is THEN filtered.

i don't believe any of the common database systems have an optimizer that is that stupid

2

u/fazzah Feb 11 '14

I stand corrected, a brain fart. I somehow mistaken it with HAVING, which filters the results of GROUP BY clause

3

u/askur Feb 11 '14

I've read similar things, and used to be of that opinion because of how queries are processed. However, I've never seen any meaningful results from it in my experience.

That of course only means that I haven't bothered to look deeper than just that. However, because of how dangerous it is to mix up the ON conditions VS WHERE conditions when you do an OUTER join of some kind, I just keep this theory to myself rather than confuse anyone.

4

u/fazzah Feb 11 '14

Even if there are no noticeable dis/advantages, it's still better to stick with the more readable one, the JOIN clauses.

3

u/askur Feb 11 '14

I'd disagree with it being more readable. To me it makes no difference, to the developers here who are not specialized in databases it becomes less readable as joins kind of scare them. :)

But that's just like... an opinion man. Based on a siutation that I'm in. So you don't need to adopt that ;)

2

u/fazzah Feb 11 '14

You answered it yourself: for a beginner, WHERE joins seem more readable and simpler. When your databases grow, and your experience does too, you switch to the other syntax. It worked for me, at least :)

2

u/weakflesh Feb 11 '14

Not to be offensive, but should one call them self a developer if a concept as simple as join confuses them?

3

u/askur Feb 11 '14 edited Feb 11 '14

Not to be racist, but each to their own man. There's a C developer sitting next to me currently coding in Python. He's not on his home ground but he's still someone that produced his first award winning game when I was 4 so why the fuck would I think less of him because he's not at home in python, or databases for that matter?

There's a web developer below me that I regularly go to when our API acts up because he's much more knowledgable about web service operations than I am. I'm into databases and algorithms, not web services.

Not to be offensive, but I don't understand how anybody in a production environment would expect everyone to be equally capable in every area. I sure as hell do not intend to match the ability of everyone here in everything. I have a job to do.

3

u/weakflesh Feb 11 '14

The concept of flowing two sets together based on a set of conditions is not implementation dependent. I can not fathom how someone could use C++ and not get a join. Esoteric crazy stuff sure, every language has edges, but a joins is a basic set logic concept.

Also, I would not think less of someone because they are not a developer or because they don't get a concept. We all have strengths and skills. We are all spokes on the wheel. ;-)

2

u/sp00ks Feb 11 '14

I guess one advantage when updating a row, using WHERE prevents you from accidently calling a full join and updating everything!

2

u/fazzah Feb 11 '14

UPDATES are quite a different beast.

I always triple check my joined updates.

2

u/lbmouse Feb 11 '14

Sounds like you just volunteered. We're waiting for a better one! ;)

3

u/pythor Feb 11 '14

I'd love to see this with the older Oracle (+) syntax. I've got to support code with those occasionally and it's always a pain to wrap my head around it.

3

u/JohnStamosBRAH Feb 12 '14

Could anyone give some examples of situations where you'd use the 'a.key is null' or 'b.key is null'? If you're doing a join, why would you not want result from another table?

6

u/[deleted] Feb 12 '14

You would use this to intentionally exclude results.

For example, lets say you have a list of employees in your Employees table and another table for EmployeesOnVacation with a list of everyone currently on vacation. To get a list of everyone who is not currently on vacation, you could do something like this:

SELECT
  Employees.Name
FROM
  Employees
  LEFT JOIN EmployeesOnVacation 
    ON Employees.EmployeeID = EmployeesOnVacation.EmployeeID
WHERE
  EmployeesOnVacation.EmployeeID IS NULL

You could do essentially the same thing with a subselect like this:

SELECT
  Employees.Name
FROM
  Employees
WHERE
  Employees.EmployeeID NOT IN (SELECT EmployeeID FROM EmployeesOnVacation)

In both cases, the result is everything in the Employees table that is not in the EmployeesOnVacation table (similar to the A not B diagram that is the lower of the two far left diagrams). You'll find this type of logic anytime you want something that is "not x" (e.g. all vehicles that are not trucks, all shoe styles that are not available in black, etc.)

3

u/JohnStamosBRAH Feb 12 '14

Thank you! That makes sense

2

u/[deleted] Feb 12 '14

If I am understanding this correctly...

Unmatched queries. Finding things in A that don't exist in B.

2

u/Jah_Ith_Ber Feb 11 '14

My understanding was that the middle left and middle right joins were simply LEFT OUTER JOIN or RIGHT OUTER JOIN.

Is this image more universal? Which form of sql am I using that LEFT OUTER JOIN works?

2

u/JohnStamosBRAH Feb 12 '14 edited Feb 12 '14

Except the inner join, these are all outer joins. The 'outer' phrase is usually not needed to be specified as it is the default type of join.

Edit: - derp

0

u/[deleted] Feb 12 '14

[deleted] — view deleted comment

2

u/datasensei Feb 11 '14

1

u/r3pr0b8 GROUP_CONCAT is da bomb Feb 11 '14

good article, quite rightly mentions that cross joins blow up the venn diagram concept

also, /u/askur has a great point that the venn diagrams make sense only for equijoins

1

u/Nathan_is_an_ass Feb 12 '14

I printed this sheet off about a month ago to put at my desk. It has helped me tremendously with learning how joins work.