Research on join on in SQL

[Switch] research on join on in SQL

From: http://hi.baidu.com/benaheng/blog/item/c7e6c0f916b3895d242df2ec.html

The number of records connected by a left join B is the same as that of Table.
The number of records connected by a right join B is the same as that of Table B.
A left join B is equivalent to B right join

Table:

 
Field_k, field_a1 A3 B4 C
 

Table B:
Field_k, field_ B

 
1x2 Y4 Z
 

Select a. field_k, A. field_a, B. field_k, B. field_ B
From a left join B on A. field_k = B. field_k

Field_k field_a field_k field_ B ---------- 1 A 1x3 B null null4 C 4 zselect. field_k,. field_a, B. field_k, B. field_bfrom a right join B on. field_k = B. field_kfield_k field_a field_k field_ B ---------- 1 A 1 x null 2 Y 4 C 4 Z
 

 
Table1: Id sex1a 1B 0
Table2: Id sex2 A 4
Select ID, sex1, sex2from Table1 left join Table2 on table1.id = table2.id then:
Id sex1 sex2a 1 4 B 0 null
 

That is to say, left join will appear for all records in the left table of the join operation. If no related records are found in Table 2 based on the join condition, the result is null.
Right join shows all records in the right table. Inner join only records that meet the conditions will appear in the result set.

Eg2 ':
There are two tables, A and B. The first two fields are identical: (ID int, name varchar (10 )...)

ID name ----------- ---------- 1 A 2 B 3 C
 

Do you know the running result of the following query statement? :
1. Select * from a left join B on A. ID = B. ID where a. ID = 1
2. Select * from a left join B on A. ID = B. ID and A. ID = 1
3. Select * from a left join B on A. ID = B. ID and B. ID = 1
4. Select * from a left join B on A. ID = 1

Result: ID name ----------- 1 10 1 10 (1 row (s) affected)
ID name ----------- 1 10 1 102 20 null null3 30 null (3 row (s) affected)
ID name ----------- 1 10 1 102 20 null null3 30 null (3 row (s) affected)
ID name ----------- 1 10 1 101 10 2 201 10 3 302 20 null null3 30 null (5 row (s) affected)
 

Ideas:

Left join, which is based on the left table and scans matching records in the right table.
First, there are 1st records in the left table.

 
1
 

According to Condition. id = 1, to scan the records in the right table for each record in the right table, obviously. the condition id = 1 is true, so the result of matching 1st records is:

 
1 A 1 A1 A 2 B1 A 3 C ---------------------------------------------
 

Then scan 2nd records

 
2 B
 

For Condition A. ID = 1, there is no matching record in the edge table, so the right table is null. Therefore, the result of matching 2nd records is

 
2 B null
 

----------------------------------------------
The matching result is the same as that of the first record.

 
3 C null
 

---------------------------------------
Therefore, the final result is five records.

 
1 A 1 A1 A 2 B1 A 3 C2 B null null3 C null
 

Inner join (a typical Join Operation uses comparison operators such as = or <> ). Including equal join and natural join. The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.
outer join: The Outer Join can be left Outer Join, right outer join, or full outer join.
when an external join is specified in the from clause, it can be specified by one of the following sets of keywords:
left join or left Outer Join. 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 selection list columns in the right table in the row of the associated result set are null.
right join or right outer join. 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.
full join or full outer join. The Complete External Join Operation 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. Cross join. Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.