Illustration: MYSQLJOINON, SQLJOIN, and database sqljoin statement _ MySQL

Source: Internet
Author: User
The illustration of MYSQLJOINON and SQLJOIN is detailed. SQL Join statements in databases 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.

  • TableIs the table on the left.
  • Table BIs the table on the right.

There are four records, two of which are the same, as shown below:

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

Let's take a 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 result set is the intersection of A and B.

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     Rutabaganull  null       3     Darth Vader

Full outer joinGenerate the union of A and B. However, it should be noted that for records without matching, null is used as the value.

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 joinGenerates 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 TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableB.id IS null id  name       id     name--  ----       --     ----2   Monkey     null   null4   Spaghetti  null   null

Generate A set that exists in Table A but not in Table B.

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

Generate A dataset that does not appear in both Table A and Table B.

We also need to register another one: the delivery set"Cross joinThere is no way to use the style graph for this Join, because it is to combine the data of Table A and Table B with N * M, that is, Cartesian product. The expression is as follows:

SELECT * FROM TableACROSS 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.

(Full text)

Related Article

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.