Drawing interpreting SQL Join statements

Ext.: http://blog.jobbole.com/40443/

This article by Bole Online-Qi Feng Yu Gu translation. without permission, no reprint!
English Source: Jeff Atwood. Welcome to join the translation team.

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 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:

MySQL

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.

Drawing interpreting SQL Join statements