Graphic tutorial on SQL Join

Source: Internet
Author: User

SQL Join may be messy during learning. We know that the Join Syntax of SQL has many inner, outer, and left clauses. Sometimes, it is not clear about the result set of Select statements. In an article on Coding Horror (it is not clear why Coding Horror is also walled), Venn diagrams explains SQL Join. I think it's easy to understand.

Suppose we have two tables.

Table A is the Table on the left.
Table B is the right Table.
There are four records, two of which are the same, as shown below:Copy codeThe Code is as follows: id name
------------
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

Let's take a look at the results of different joins.

SELECT * FROM TableA
Inner join TableB
ON TableA. name = TableB. name

Id name
------------
1 Pirate 2 Pirate
3 Ninja 4 NinjaInner join
The result set is the intersection of A and B.

SELECT * FROM TableA
Full outer join TableB
ON TableA. name = TableB. name

Id name
------------
1 Pirate 2 Pirate
2 Monkey null
3 Ninja 4 Ninja
4 Spaghetti null
Null 1 Rutabaga
Null 3 Darth VaderFull outer join generates the Union of A and B. However, it should be noted that for records without matching, null is used as the value.

SELECT * FROM TableA
Left outer join TableB
ON TableA. name = TableB. name

Id name
------------
1 Pirate 2 Pirate
2 Monkey null
3 Ninja 4 Ninja
4 Spaghetti null nullLeft outer join generates A complete set of Table A, while Table B matches with A value. If no match exists, it is replaced by A null value.

SELECT * FROM TableA
Left outer join TableB
ON TableA. name = TableB. name
WHERE TableB. id IS null

Id name
------------
2 Monkey null
4 Spaghetti null is A set that exists in Table A and does not exist in table B.

SELECT * FROM TableA
Full outer join TableB
ON TableA. name = TableB. name
WHERE TableA. id IS null
OR TableB. id IS null

Id name
------------
2 Monkey null
4 Spaghetti null
Null 1 Rutabaga
Null 3 Darth Vader generates datasets that are not present in both table A and table B.

We also need to register another cross join, which is the "Intersection Set". This Join cannot be represented by a text graph, because it combines the data in tables A and B with N * M, that is, Cartesian product. The expression is as follows:Copy codeThe Code is as follows: SELECT * FROM TableA
Cross join TableB

This Cartesian product generates 4x4 = 16 records. In general, this syntax is rarely used. However, we should be careful that, if we do not use nested select statements, the system will generate Cartesian Product and then filter again. This is very dangerous for performance, especially when the table is large.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.