3

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
and
https://web.archive.org/web/20120621231245/http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/

have been very helpful in learning the basics of joins using Venn diagrams. But I am wondering how you would apply that same thinking to a query that has more than one join.

Let's say I have 3 tables:

Employees

EmployeeID
FullName
EmployeeTypeID

EmployeeTypes (full time, part time, etc.)

EmployeeTypeID
TypeName

InsuranceRecords

InsuranceRecordID
EmployeeID
HealthInsuranceNumber

Now, I want my final result set to include data from all three tables, in this format:

EmployeeID | FullName | TypeName | HealthInsuranceNumber

Using what I learned from those two sites, I can use the following joins to get all employees, regardless of whether or not their insurance information exists or not:

SELECT 
    Employees.EmployeeID, FullName, TypeName, HealthInsuranceNumber 
FROM Employees
INNER JOIN EmployeeTypes ON Employees.EmployeeTypeID = EmployeeTypes.EmployeeTypeID
LEFT OUTER JOIN InsuranceRecords ON Employees.EmployeeID = InsuranceRecords.EmployeeID

My question is, using the same kind of Venn diagram pattern, how would the above query be represented visually? Is this picture accurate?

multiple joins

philipxy
  • 14,867
  • 6
  • 39
  • 83
CptSupermrkt
  • 6,844
  • 12
  • 56
  • 87
  • 2
    Not to be to picky but that's a Euler diagram, not a Venn diagram. – Jesse Jul 30 '12 at 06:58
  • Venn diagrams don't show joins. Tables are bags of rows not sets of rows. The author of the 1st linked blog repudiates it in the comments on it. Very special cases of SQL can correspond to Venn diagrams but notice that such diagrams never come with such restrictions & a legend. Why do you even accept a diagram with no legend?--Rhetorical. [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) "have been very helpful in learning the basics of joins using Venn diagrams" Oh? What are the circles & elements? What if there are duplicate rows? Or nulls? What does it say & why? – philipxy Aug 19 '22 at 08:56

2 Answers2

3

I think it is not quite possible to map your example onto these types of diagrams for the following reason:

The diagrams here are diagrams used to describe intersections and unions in set theory. For the purpose of having an overlap as depicted in the diagrams, all three diagrams need to contain elements of the same type which by definition is not possible if we are dealing with three different tables where each contains a different type of (row-)object.

If all three tables would be joined on the same key then you could identify the values of this key as the elements the sets contain but since this is not the case in your example these kind of pictures are not applicable.

If we do assume that in your example both joins use the same key, then only the green area would be the correct result since the first join restricts you to the intersection of Employees and Employee types and the second join restricts you the all of Employees and since both join conditions must be true you would get the intersection of both of the aforementioned sections which is the green area.

Hope this helps.

Cornelius
  • 640
  • 8
  • 22
1

That is not an accurate set diagram (either Venn or Euler). There are no entities which are members of both Employees and Employee Types. Even if your table schema represented some kind of table-inheritance, all the entities would still be in a base table.

Jeff's example on the Coding Horror blog only works with like entities i.e. two tables containing the same entities - technically a violation of normalization - or a self-join.

Venn diagrams can accurately depict scenarios like:

-- The intersection lens
SELECT *
FROM table
WHERE condition1
    AND condition2

-- One of the lunes
SELECT *
FROM table
WHERE condition1
    AND NOT condition2

-- The union
SELECT *
FROM table
WHERE condition1
    OR condition2
Cade Roux
  • 88,164
  • 40
  • 182
  • 265