Various join joins for SQL

For SQL joins, learning may be a bit confusing. We know that the join syntax for SQL has a lot of inner, outer, left, and sometimes it's not very clear what the result set looks like for a select. There is an article on Coding horror (it is not clear why Coding horror was also the wall) through the Venturi diagram Venn diagrams explained the join of SQL. I feel clear and understandable, turn around.

Suppose we have two tables, table A is the one on the left and table B is the one on the right. What is the Venetian Macao?

Each of them has four records, of which two records are the same, as follows:

1	id nameid  name

2	-- ----       --  ----

3	1  Pirate     1   Rutabaga

4	2  Monkey     2   Pirate

5	3  Ninja      3   Darth Vader

6	4  Spaghetti  4   Ninja

Let's look at the results of different joins.

1 SELECT* FROM TableA INNERJOINTableB 2 ONTableA.name= TableB.name 3 id  nameid   name 4 --  ----       --   ---- 5 1   Pirate     2    Pirate 6 3   Ninja      4    Ninja Inner Join The resulting set of results is the intersection of a and B.
01 SELECT* FROM TableA FULLOUTER JOINTableB 02 ONTableA.name= TableB.name 03 id    nameid    name 04 --    ----       --    ---- 05 1     Pirate     2     Pirate 06 2     Monkey     nullnull 07 3     Ninja      4     Ninja 08 4     Spaghetti  nullnull 09 nullnull1     Rutabaga 10 nullnull3     Darth Vader The full outer join produces a and B's set. It is important to note, however, that for records that do not have a match, NULL is the value.
1 SELECT* FROM TableA LEFTOUTER JOIN TableB ONTableA.name= TableB.name 2 id  nameid    name 3 --  ----       --    ---- 4 1   Pirate     2     Pirate 5 2   Monkey      nullnull 6 3   Ninja      4     Ninja 7 4   Spaghetti   nullnull The left outer join produces a full set of table A, whereas a match in B table has a value, and no match is substituted with a null value.
1 select *  from  tablea   left outer  join  tableb  on tablea. name  = TableB. name where  tableb.id  is  null 2 id  nameid     name 3 --  ----       --     ---- 4 2   Monkey  nullnull 5 4   Spaghetti   nullnull Produces a collection that is available in table A and not in the B table.
1 select *  from  tablea   full outer  join  tableb  on tablea. name  = TableB. name where  tablea.id  is  null  or tableb.id   is  null 2 id    nameid    name 3 --    ----       --    ---- 4 2     Monkey    nullnull 5 4     Spaghetti nullnull 6 nullnull1     Rutabaga 7 nullnull3     Darth Vader produce datasets that do not appear in both A and B tables.

It is also necessary to register that we also have a cross join of "cross-set", which is not represented by Wenshitu because it is a n*m combination of the data of table A and table B, that is, the Cartesian product. The expression is as follows:

1	SELECT* FROMTableA

2	CROSSJOINTableB

This Cartesian product produces 4 x 4 = 16 records, which, in general, are seldom used in this syntax. But we have to be careful, if you do not use nested SELECT statements, the general system will produce a Cartesian product and then filter. This is very dangerous for performance, especially when the table is very large.

Various join joins for SQL