Graph SQL joins to Coolshell

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.

Let's say we have two tables.

• Table A is the sheet on the left.
• Table B is the list on the right.

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

ID name       ID  name------       --  ----1  Pirate     1   Rutabaga2  Monkey     2   Pirate3  Ninja      3   Darth Vader4  spaghetti  4   Ninja

Let's look at the results of different joins.

SELECT * from TableAINNER JOIN tablebon tablea.name = Tableb.nameid name ID name-- ---- - - ----1 Pirate 2 Pirate3 Ninja 4 Ninja Inner Join The resulting set of results is the intersection of a and B.
SELECT * from TableA fullOUTER JOIN tablebon tablea.name = Tableb.nameid name ID name-- ---- -- ----1 Pirate 2 Pirate2 Monkey null null3 Ninja 4 Ninja4 Spaghetti null nullnull null 1 rutabaganull null 3 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.
SELECT * from TableA leftOUTER JOIN tablebon tablea.name = Tableb.nameid name ID name-- ---- c5/>-- ----1 Pirate 2 Pirate2 Monkey null null3 Ninja 4 Ninja4 spaghetti null NULL 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.
SELECT * from Tablealeft OUTER JOIN tablebon tablea.name = tableb.nameWHERE tableb.id is null ID name id name-- ---- -- ----2 Monkey null null4 spaghetti null Null Produces a collection that is available in table A and not in the B table.
SELECT * from Tableafull OUTER JOIN tablebon tablea.name = tableb.nameWHERE tablea.id are nullor tableb.id is null ID name ID name-- ---- -- ----2 Monkey null null4 spaghetti null nullnull null 1 rutabaganull null 3 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 joinof "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:

SELECT * from TableA crossJOIN TableB

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.

Updated: March 30, 2014

(End of full text)

This article is reproduced from Coolshell blog original address

Graph SQL joins to Coolshell