Drawing interpreting SQL Join statements

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   Rutabaga2  Monkey     2   Pirate3  Ninja      3   Darth Vader4  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 TableAINNER JOIN TableBON TableA.name = TableB.nameid  name       id   name--  ----       --   ----1   Pirate     2    Pirate3   Ninja      4    Ninja

Inner Union (inner join) only generates recordsets that match both table A and table B. such as

——————————————————————————-

123456789101112

SELECT * FROM TableAFULL OUTER JOIN TableBON TableA.name = TableB.nameid    name       id    name--    ----       --    ----1     Pirate     2     Pirate2     Monkey     null  null3     Ninja      4     Ninja4     Spaghetti  null  nullnull  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 TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableB.id IS nullid  name       id     name--  ----       --     ----2   Monkey     null   null4   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 TableACROSS JOINTableB

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