# Diagram of the difference between SQL inner join, left JOIN, right join, full outer join, Union, UNION ALL

Source: Internet
Author: User

Transferred from: http://blog.csdn.net/jz20110918/article/details/41806611

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 name is the same, as follows: Let's look at the different joins

 Table A Id Name 1 Pirate 2 Monkey 3 Ninja 4 Spaghetti
 b table Id Name 1 Rutabaga 2 Pirate 3 Darth Vade 4 Ninja

1.INNER JOIN

SELECT * from TableA INNER JOIN TableB on tablea.name = Tableb.name

 Result set (TableA.) (TableB.) Id Name Id Name 1 Pirate 2 Pirate 3 Ninja 4 Ninja

The result set produced by the Inner join is the intersection of a and B.

2.FULL [OUTER] JOIN(1) SELECT * from TableA Full OUTER JOINTableB on tablea.name = Tableb.name Col span= "4" width= "\$" >
 result set (TableA.) ID name ID name 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti NULL NULL null null 1 rutabaga null null 3 Darth vade
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. Can be judged using ifnull. (2) SELECT * from TableA Full OUTER JOINTableB on tablea.name = Tableb.name
WHERE tablea.id is null OR tableb.id is null
 Result set (TableA.) (TableB.) Id Name Id Name 2 Monkey Null Null 4 Spaghetti Null Null Null Null 1 Rutabaga Null Null 3 Darth Vade
produces datasets that have no intersection between A and B tables. 3.LEFT [OUTER] JOIN(1) SELECT * from TableA Left OUTER JOINTableB on tablea.name = Tableb.name
 Result set (TableA.) (TableB.) Id Name Id Name 1 Pirate 2 Pirate 2 Monkey Null Null 3 Ninja 4 Ninja 4 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. (2) SELECT * from TableA Left OUTER JOINTableB on tablea.name = Tableb.name WHERE tableb.id is null
 Result set (TableA.) (TableB.) Id Name Id Name 2 Monkey Null Null 4 Spaghetti Null Null

Produces a collection that is available in table A and not in the B table.

4.RIGHT [OUTER] JOINRight OUTER  Join is based on the following table, similar to the left OUTER join usage. This is not covered here. 5.UNIONAnd UNION AllThe UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same. Union selects only records, and union all lists all records. (1) SELECT name from TableA UNIONSELECT name from TableB
 New result set Name Pirate Monkey Ninja Spaghetti Rutabaga Darth Vade
Choose a different value (2) select name from TableA UNION AllSELECT name from TableB
 New result set Name Pirate Monkey Ninja Spaghetti Rutabaga Pirate Darth Vade Ninja

All listed.

(3) Note:

SELECT * from TableA UNIONSELECT * from TableB
 New result set Id Name 1 Pirate 2 Monkey 3 Ninja 4 Spaghetti 1 Rutabaga 2 Pirate 3 Darth Vade 4 Ninja
Since ID 1 Pirate is not the same as ID 2 Pirate, it is also necessary to register for non-merging we also have a "cross set" cross join, which has no way to be represented by Wenshitu because it is a combination of a n*m of the data of table A and table B, that is, the Cartesian product. The expression is as follows: SELECT * from TableA cross JOIN 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.

The difference between the inner join, left JOIN, right join, full outer join, Union, UNION All of the graphical SQL

Related Keywords:

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.