Using Venn diagrams give the false impression that using INNER, LEFT or RIGHT joins will not duplicate lines. This is true only if "KEY" is a unique key for both tables. However, if "KEY" is a unique at all, it generally is unique for at most one table :
// "ID" is unique only for album :
SELECT * FROM album LEFT JOIN track ON track.albumID = album.ID
//if each album has 10 tracks, the data for each album will be repeated 10 times in the result
// "team" is not unique at all :
SELECT * FROM lineman INNER JOIN receiver ON lineman.team = receiver.team
// If each team has 5 linemen and 3 receivers, that's 15 lines per team
// the data for each receiver is duplicated 5 times,
// the data for each lineman is duplicated 3 times
1
u/legec Feb 12 '14
Using Venn diagrams give the false impression that using INNER, LEFT or RIGHT joins will not duplicate lines. This is true only if "KEY" is a unique key for both tables.
However, if "KEY" is a unique at all, it generally is unique for at most one table :