Three types of joins in Ms-sql and their differences from merging

Test platform: Ms-sql R2;

The following two t_a and T_b tables are tested for joins and all-

First: Create a table

CREATE table T_a ([aid] [Int],[anum]  [char])

Second: Create B Table

CREATE TABLE T_b ([bid] [int],[aname]  [char])

Third: 1: Add data to Table A

Insert into T_a (aid,anum) SELECT 1,20050111 unionselect 2,20050112 unionselect 3,20050113 unionselect 4,20050114union All SELECT 5,20050115

A table data is as follows

Aid Anum

1 20050111

2 20050112

3 20050113

4 20050114

5 20050115

2 : Give B Table Add Data

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

Table B data is as follows

Bid bname

1 2006032401

2 2006032402

3 2006032403

4 2006032404

8 2006032408

IV: 1: Left join as follows

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 NULL

2: Right Join below

SELECT * from t_a right join T_b on T_a.aid=t_b.bid

The right join data appears as follows

Aid Anum Bid Bname

1 20050111 1 2006032401

2 20050112 2 2006032402

3 20050113 3 2006032403

4 20050114 4 2006032404

NULL NULL 8 2006032408

Summary: The table before 1:join is arranged in front, the table after join is behind;

2: Leftist is based on the data, right-hand is to the right of the data to prevail;

3 : Inner Join as follows

SELECT * FROM t_a inner join t_b on t_a.aid = T_b.bid

Shown below:

Aid Anum Bid bname

1 20050111 1 2006032401

2 20050112 2 2006032402

3 20050113 3 2006032403

4 20050114 4 2006032404

Summary: The difference between inner join and right join and Leftjoin, only the number of aid=bid rows is displayed, others are not displayed;

Merge the two tables of t_a and T_b

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

The difference between merging and joining

1: The merge is added vertically, but the rows are displayed in two rows;

2: The connection is lateral plus, just on the left or right side of the data;

Note: Some of the actions I have seen in this process are summarized in addition

One: When deleting two cases of data, use an and

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

Second: Modify the example name of a case table

1: Delete the Name column of a table

2: Add a bname column in a table

ALTER TABLE T_badd bname int

Three: The data definition type of a column such as a12345 (the next one with parentheses containing 5 should not be written inside the brackets)

CREATE TABLE t_test ([id] [char] (5)  )

Three types of joins in Ms-sql and their differences from merging