"Go" plots various join joins of SQL

Original address: http://www.nowamagic.net/librarys/veda/detail/936 graphical SQL connection join allows you to connect to SQL at a glance

It was written on the day of December 22, 2011, and it was read 16,729 times.

Thanks for reference or original text

Server June spent a total of 16.289 MS 2 database queries, and strive to provide you with this page.

Try reading mode? I'd like to hear your suggestions.

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.

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 LEFTOUTER JOIN TableB ONTableA.name = TableB.nameWHERE TableB.id ISnull

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 FULLOUTER JOIN TableB ONTableA.name = TableB.nameWHERE TableA.id IS null ORTableB.id ISnull

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.

"Go" plots various join joins of SQL