The difference between the three connections and the merger in MS-SQL,

The difference between the three connections and the merger in MS-SQL,

Test Platform: MS-SQL 2008 R2;

The following two t_a and t_ B tables are used to test join and full join.

1. Create Table

create table t_a([aid] [int],[anum]  [char])

2. Create Table B

create table t_b([bid] [int],[aname]  [char])

Third: 1: add data to Table

insert INTO t_a(aid,anum) SELECT 1,20050111 unionselect 2,20050112 unionselect 3,20050113 unionselect 4,20050114union ALL SELECT 5,20050115

The data in table A is as follows:

Aid anum

1 20050111

2 20050112

3 20050113

4 20050114

5 20050115

 

2: add data to table B

insertINTO t_b(bid,bname) SELECT 1,2006032401 unionselect 2,2006032402 unionselect 3,2006032403 unionselect 4,2006032404union ALL SELECT 8,2006032408

The data in table B is as follows:

Bid bname

1 2006032401

2 2006032402

3 2006032403

4 2006032404

8 2006032408

4: 1: left join:

select * from t_a left join t_b on t_a.aid=t_b.bid

The left join data is as follows:

Aid anum bid bname

1 20050111 1 2006032401

2 20050112 2 2006032402

3 20050113 3 2006032403

4 20050114 4 2006032404

5 20050115 NULL

2: Right join:

select * from t_a right join t_b on t_a.aid=t_b.bid

The right join data is shown as follows:

Aid anum bid bname

1 20050111 1 2006032401

2 20050112 2 2006032402

3 20050113 3 2006032403

4 20050114 4 2006032404

NULL 8 2006032408

Conclusion: 1: The tables before the join operation are arranged in the front, and the tables after the join operation are arranged in the back;

2: The left link is based on the left and right data, and the right link is based on the data on the right;

3: Inner join:

select * from t_a inner join t_b on t_a.aID = t_b.bID

Shown as follows:

Aid anum bid bname

1 20050111 1 2006032401

2 20050112 2 2006032402

3 20050113 3 2006032403

4 20050114 4 2006032404

Conclusion: The differences between inner join and right join and leftjoin are as follows: only the number of lines with aid = bid is displayed, and no other lines are displayed;

Merge the t_a and t_ B tables.

select * from t_bunionselect * from t_a

Bid bname

1 20050111

1 2006032401

2 20050112

2 2006032402

3 20050113

3 2006032403

4 20050114

4 2006032404

5 20050115

8 2006032408

Differences between merge and join

1: Merge is a vertical addition, but the rows of the opposite rows are displayed as two rows;

2: Join is a horizontal addition; only data on the left or right is used;

Note: Some operations I have made in this process are also summarized.

1. Use an and When deleting data in two cases

delete  from t_a where aid='1' and anum='20050111'

2. Modify the Instance name of an example table

1: delete the name column of a table

alter table t_bdrop colum aname 

2: Add a bname column in the table.

alter table t_badd bname int

Iii. Data Definition type of columns like a12345)

create table t_test ( [id] [char](5)  )