SQL Server join, left join, right join, outer join, and Union

Let's give a general explanation.
Example Table
Aid adate
1 A1
2 A2
3 A3
Table B
Bid bdate
1 B1
2 B2
4 B4
Two tables A and B are connected. fields with the same ID must be retrieved.
Select * from a inner join B on A. Aid = B. Bid this is only used to retrieve matching data.
In this case, the following information is taken:
1 A1 B1
2 A2 B2
Then left join refers:
Select * from a left join B on A. Aid = B. Bid
First, retrieve all the data in Table A, and then add the data that matches table A and table B.
In this case, the following information is taken:
1 A1 B1
2 A2 B2
3 A3 null characters
Right join is also available.
This means that all data in Table B is retrieved first, and then the data matching A and B is added.
In this case, the following information is taken:
1 A1 B1
2 A2 B2
4 blank characters B4

"Outer Join
This join method is rarely used by ordinary people, and even some SQL managers have never used it. This is really a sad representative, because using outer join can simplify some query work, let's first look at the outer join syntax.
Select <field to be queried> from
[Outer] Join on
Outer In the syntax can be omitted. For example, you can use left join or right join. In essence, outer join is inclusive! Different from the exclusive nature of inner join, the query results in left Outer Join will contain information about all left tables, right outer join queries will contain all right data tables."

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.

 

Union: by default, Union removes duplicates, while union all displays all duplicates.

Select a, L = count (*) from table where a = 1 group by
Union
Select B, L = count (*) from table where B = 2 group by B
Union
Select B, L = count (*) from table where c = 3 group by C

Union takes the Union of several SELECT statement results and removes duplicate records. The number and type of fields in the SELECT statement must be consistent.