I was looking at a few graphs to understand the difference between the joins, and I came across this image:
Maybe the problem is in representing this with Venn Diagrams.
But looking at the first join, top left, isn't that just A?
What difference does B make?
No, since it's a join, it can produce cardinalities greater than 1 for the rows in A. That is, if there are multiple matching rows from B then a row in A will show up multiple times.
Example:
Table A:
id name
-- -------
1 Alice
2 Malcolm
3 Kelly
Table B:
id_a preferred_food
---- --------------
1 Pizza
2 Burger
2 Steak
2 Minestroni
Then "A left join B" will give you:
id name id_a preferred_food
-- ------- ---- --------------
1 Alice 1 Pizza
2 Malcolm 2 Burger
2 Malcolm 2 Steak
2 Malcolm 2 Minestroni
3 Kelly null null
In short:
3 Kelly shows up.3 Kelly has null in the last two columns.2 Malcolm shows up three times.Your diagram isn't quite a Venn diagram.
The intersection of the two circles represents joined rows (according to your join condition) with data from both table A and table B.
The left crescent (labeled "A") represents rows in table A that do not have any corresponding rows in table B; the right crescent (labeled "B") represents rows in table B that do not have any corresponding rows in table A.
What the top left diagram is supposed to show is that a left join gives you data from both table A and B that can be joined up according to your join condition, plus all rows from table A that have no corresponding match in table B.
With JOINs, you get fields from both tables, not only A. It also multiplies the number of records returned if the relationship between tables is not 1-1.
Basically, the only difference between all those JOIN is the behavior when records in A do not match any records in B and vice versa. Your diagrams only show that behavior.
Diagram top let = keep all the records from A, whether or not they match to anything in B (+ what is not represented: when they match, get the data from B).
A LEFT JOIN B MEANS A + (common ENTRIES OF A WHICH ARE THERE IN B) left join would rather be performed where the table which is left joined has more records in this case A and those records might refer some other columns from B on some common condition. hence, the result A left join B would not mean only A but columns of B added as well in case if required on some common value of A and B.
In that diagram they omit the word 'outer' from that description, but you should read it as LEFT OUTER JOIN.
The table to the "left" of the SQL statement (which is going to be the table after the FROM, and in this case, table A) will have every row returned whether or not there is a corresponding row in Table B that matches the JOIN condition.
This is the difference between an inner join and an outer join. Inner joins only return a row where there is a match on the join condition, whereas, a LEFT OUTER join returns the same rows returned by an INNER join, as well as a ROW for any rows in the LEFT table that don't satisfy the JOIN condition. For those rows in the LEFT table that don't JOIN to one or more rows in Table B, the columns of table B that might be specified in the SELECT will be NULL.
You can see that this property of unmatched LEFT rows having NULL values for table B columns is used in the example directly below to filter out any rows derived from a successful join to table B.
No, SQL A left join B, isn't just A.
In the Venn diagram depicted in the OP, the first join (top left) is a left join. A left join returns all rows from the left table (A), and the matching rows from the right table (B). If there are no matching rows in the right table, the left table rows are returned with NULL values for the columns from the right table.
In the Venn diagram, the left table is represented by the circle on the left, and the right table is represented by the circle on the right. The intersection of the two circles represents the rows that are in both tables. The left join returns all of the rows in the left circle, and the matching rows in the right circle. The rows in the left circle that do not have a matching row in the right circle are returned with NULL values for the columns from the right table.
So, in the Venn diagram, the top left join returns all of the rows in the left circle, which is the table A. The rows in the right circle that do not have a matching row in the left circle are not returned.
The confusion may arise when looking at a Venn diagram representation of a left join. Venn diagrams are typically used to represent set operations like union, intersection, and difference, which can be related to SQL operations like UNION, INTERSECT, and EXCEPT. However, when it comes to joins, Venn diagrams may not accurately represent the concept.
In a Venn diagram, the overlapping region represents the intersection of sets. But in a left join, the overlapping region would actually represent the matching rows between table A and table B. The left table (A) would be represented by a circle, and the right table (B) would be represented by another circle. The overlapping region would show the rows that have a match based on the join condition.
To summarize: Venn diagrams are OK as long as they make clear that the graphic representation of UNION, INTERSECT, EXCEPT set operations to describe a JOIN, refer to the cartesian products of table A and table B, not to the table themselves.
© 2022 - 2024 — McMap. All rights reserved.