The left, right, and Inner Connections of SQL can be one-to-many. Multiple to one. Many-to-many

Source: Internet
Author: User
Tags null null

SQL internal connection and external connection

Reproduced in: http://323229113.blog.163.com/blog/static/2185362820070172553209/

Thanks to the author.

Said:

Left join: Keep all rows on the left. Check whether the order on the left is equal to that on the right. Concatenate a row if they are equal. Yes

Right join: Keep all rows on the right. Check whether the order on the right is equal to that on the left. Concatenate a row if they are equal. Yes

Inner join: do not retain unequal rows. If the left and right sides are equal, connect. If they are not equal, remove them. Yes

Such as table
-------------------------------------------------
Table1 | Table2 |
-------------------------------------------------
ID name | ID score |
1 Lee | 1 90 |
2 Zhang | 2 100 |
4 Wang | 3 70 |
-------------------------------------------------

Run the following commands in the query Analyzer:

1. External Connection
1. Concept: including left Outer Join, right Outer Join or complete external join

2. Left join: left join or left Outer Join
(1) The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the row of the associated result set are null ).
(2) SQL statements
Select * From Table1 left join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null

//////////////////////////////////////// ///////////////

The connection can also be a non-primary key. Left join: one to multiple. Multiple to one. Yes. For example

ID name TT | id cc xx

1 S1 T1 1 T1 xx1

2 S2 T1 2 T2 xx2

3 S3 T2 3 T1 xx3

 

Left join: Select * From table1as a left join Table2 as B on A. Name = B. CC

ID name tt id cc xx

1 S1 T1 1 T1 xx1

1 S1 T1 3 T1 xx3

2 S2 T1 1 T1 xx1

2 S2 T1 3 T1 xx3

3 S3 T2 2 T2 xx2

 

 

 


------------------------------
Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

3. Right join: Right join or right Outer Join
(1) The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
(2) SQL statements
Select * From Table1 right join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
Null null 3 70
------------------------------
Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

4. Complete External join: Full join or full outer join
(1) The Complete External join returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.
(2) SQL statements
Select * From Table1 full join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang null
Null null 3 70
------------------------------
Note: return the Union of left and right connections (see upper left and right connections)

2. Internal Connection
1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. Inner join: Join or inner join

3. SQL statements
Select * From Table1 join Table2 on table1.id = table2.id
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------
Note: Only the Table1 and Table2 columns that meet the conditions are returned.

4. equivalent (same as the following execution)
A: select a. *, B. * From Table1 A, Table2 B where a. ID = B. ID
B: Select * From Table1 cross join Table2 where table1.id = table2.id (Note: cross join can only use where, not on)

Iii. Cross-join (complete)

1. Concept: A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table1 and Table2 generate 3*3 = 9 records)

2. Cross join: cross join (without the condition where ...)

3. SQL statements
Select * From Table1 cross join Table2
------------- Result -------------
ID name ID score
------------------------------
1 Lee 1 90
2 Zhang 1 90
4 wang 1 90
1 Lee 2 100
2 Zhang 2 100
4 Wang 2 100
1 Lee 3 70
2 Zhang 3 70
4 Wang 3 70
------------------------------
Note: 3*3 = 9 records are returned, that is, Cartesian product.

4. equivalent (same as the following execution)
A: Select * From Table1, Table2

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.