I think Ligaya Turmelle's post on SQL Union (join) statements is a good piece of material for a novice developer. SQL Union statements appear to be based on collections, and it is natural to use the Wayne diagram to explain how I see it. However, as stated in the reply to her post, I found in the test that the Wayne graph was not quite a match for the SQL Union syntax.
But I still like the idea, so let's see if we can use the Wayne Chart. Let's say we have the following two tables. Table A is on the left and Table B is on the right. We give them four records each.
123456 |
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja |
We used the name field to unite these tables in several different ways to see if we could get a conceptual match with those pretty Wayne graphs.
12345678 |
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja |
Inner Union (inner join) only generates recordsets that match both table A and table B. such as
——————————————————————————-
123456789101112 |
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
|
The full outer join generates a complete collection of records in table A and table B, including records that match both sides. If there is no match on one side, the missing side is null. such as
——————————————————————————-
12345678910 |
select * from TableA left OUTER JOIN TableB on tablea.name = tableb.name id name id name -- ---- -- ---- 1 pirate 2 Pirate 2 monkey null null 3 ninja 4 Ninja 4 spaghetti null null |
The left OUTER join generates all the records for table A, including records that match in table B. If there is no match, the right side will be null. such as
——————————————————————————-
123456789 |
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null |
In order to generate a recordset that is only in table A and not in table B, we use the same left-hand union, and then use the where statement to exclude records that we do not want. such as
——————————————————————————-
123456789101112 |
select * from TableA full OUTER JOIN TableB on tablea.name = tableb.name where tablea.id is null /code>or tableb.id is null id name id name -- ---- -- ---- 2 monkey null null 4 spaghetti null null null null 1 rutabaga null null 3 Darth Vader |
To generate a recordset that is unique to both table A and table B, we use the same all-out union, and then use the where statement to exclude records that are not desired on either side. such as
———————————————————–
There is also a Cartesian product or cross join, which, as far as I know, cannot be represented by the Wayne graph:
12 |
SELECT * FROM TableA CROSS JOIN TableB |
This joins "All" to "all", producing 4 times 4=16 rows, far more than the original set. If you have studied maths, you know why it is dangerous to meet a large watch.
The "2013-06-17 Update" was made by Moffatt in 2008 (click to see a larger image). Ps:jeff Atwood's article was written in 2007.
Jeff Atwood, compilation: Bole online –@ odd Wind Yu gu
Link: http://blog.jobbole.com/40443/
Drawing interpreting SQL Join statements