Visual interpretation of SQL Union Statements _ MySQL

I think LigayaTurmelle's post on SQL join statements is a good material for new developers. The SQL Union statement seems to be based on the set. it is natural to use the Wayne diagram to explain it. However, as mentioned in her post's reply, in the test, I found that Wayne graph does not match the SQL Union syntax very well. But I still like this idea, so let's see if we can use the Wayne diagram. Suppose we have the following two tables. Table A is on the left and Table B is on the right. We believe that Ligaya Turmelle's post on SQL join statements is a good material for new developers. The SQL Union statement seems to be based on the set. it is natural to use the Wayne diagram to explain it. However, as mentioned in her post's reply, in the test, I found that Wayne graph does not match the SQL Union syntax very well.

But I still like this idea, so let's see if we can use the Wayne diagram. Suppose 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.

id name       id  name-- ----       --  ----1  Pirate     1   Rutabaga2  Monkey     2   Pirate3  Ninja      3   Darth Vader4  Spaghetti  4   Ninja

We have used the name field to combine these tables in several different ways to see if they can match those beautiful Wayne diagrams in terms of concept.

SELECT * FROM TableAINNER JOIN TableBON TableA.name = TableB.nameid  name       id   name--  ----       --   ----1   Pirate     2    Pirate3   Ninja      4    Ninja

Inner join only generates records that match both Table A and Table B. (For example)

---------------------------

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

Full outer join generates the complete set of records in tables A and B, including records matching both sides. If one side does not match, the missing side is null. (For example)

---------------------------

SELECT * FROM TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameid  name       id    name--  ----       --    ----1   Pirate     2     Pirate2   Monkey     null  null3   Ninja      4     Ninja4   Spaghetti  null  null

Left outer join generates all records of Table A, including matching records in Table B. If no match exists, the right side is null. (For example)

---------------------------

SELECT * FROM TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableB.id IS nullid  name       id     name--  ----       --     ----2   Monkey     null   null4   Spaghetti  null   null

To generate A record set that is only in Table A but not in Table B, we use the same left outer union and then use the where statement to exclude records we don't want. (For example)

---------------------------

SELECT * FROM TableAFULL OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableA.id IS null OR TableB.id IS nullid    name       id    name--    ----       --    ----2     Monkey     null  null4     Spaghetti  null  nullnull  null       1     Rutabaganull  null       3     Darth Vader

To generate A unique record set for tables A and B, we use the same full outer union and then use the where statement to exclude records that are not expected on both sides. (For example)

--------------------

There is also a Cartesian product orCross join)As far as I know, it cannot be represented by Wayne diagram:

SELECT * FROM TableACROSS JOIN TableB

This connects "all" to "all" and generates 4 × 4 = 16 rows, far more than the original set. If you have studied mathematics, you will know why this association is dangerous to large tables.

Original English; Jeff Atwood, compilation: Bole online-@ Qifeng Yugu

Http://blog.jobbole.com/40443/.