Shulou(Shulou.com)06/01 Report--

This article mainly introduces SQL SERVER how to achieve join and merge query, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Create a test table MyStudentInfoCREATE table MyStudentInfo (Id int not null primary key,Name varchar (16), Age int,Gender varchar (2), Phone varchar (16), Address varchar (50), GradeId int) to jointly insert multiple pieces of data: INSERT INTO MyStudentInfoSELECT 1 minus' Zhang San', 20 minicales 1, 589 12, 1 UNIONSELECT 2, Li 4, 22 1, 12345 678901, 1 UNIONSELECT 3, Wang Wu, 16, 13976891234, 2 UNIONSELECT 4 'Zhao Liu', '18676891234', 'Chongqing', 3 UNIONSELECT 5 'Xiaohong', 21 '777 6891234' Guangzhou', 4 UNIONSELECT 6 'Xiao Wang', 25'13 768 91234 'Shenzhen', 5 UNIONSELECT 7 'Xiao Liu', 18 UNIONSELECT 8 'Xiao Zhang', 6 UNIONSELECT 8 'Xiao Zhang', 16 'Zhong' 13974596734 'Changsha', 6 UNIONSELECT 9 'Xiao Luo', 27 people'1' '13175122786' Wuhan', 7 UNIONSELECT 10 'Xiao Yuan', 21 '715872346' Shijiazhuang', 8 create test table GradeInfoCREATE table GradeInfo (Id int not null primary key,GradeName varchar (16))

Jointly insert multiple pieces of data

INSERT INTO GradeInfoSELECT 1. Net 'UNIONSELECT 2. Net' UNIONSELECT 3, PHP 'UNIONSELECT 4, UI' UNIONSELECT 5, HTML 5, UNIONSELECT 6, Java, UNIONSELECT 9, Hadoop, UNIONSELECT 10, big data.

The inner link deletes all rows that are not matched in the linked table from the result table, so we say that the inner link may lose data.

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeNameFROM MyStudentInfo s INNER JOIN GradeInfo g ON s.GradeId=g.Id

External link

Left outer join, keyword LEFT JOIN

Left outer join, the result set includes all rows in the left table, and if a row of the left table does not match in the right table, it is in the associated result set row

All selection lists from the table on the right are NULL

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeNameFROM MyStudentInfo s LEFT JOIN GradeInfo g ON s.GradeId=g.Id

Right outer connection

Join with GIGHT JOIN

Is a reverse join of the left outer join, which returns all rows of the right table. If a row of the right table does not match in the left table, it returns NULL for the left table.

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeNameFROM MyStudentInfo s RIGHT JOIN GradeInfo g ON s.GradeId=g.Id

Complete outer connection

The keyword is FULL JOIN

The full outer join uses FULL JOIN to join. When a row has no matching row in another table, the select list of the other table will return NULL

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeNameFROM MyStudentInfo s FULL JOIN GradeInfo g ON s.GradeId=g.Id

Cross join (Cartesian product)

CORSS JOIN has neither where nor on, and the number of rows returned is the product of the number of rows in two tables.

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeNameFROM MyStudentInfo s CROSS JOIN GradeInfo g

The UNION:UNION operator is used to merge the result sets of two or more SELECT statements

UNION must follow

1. The number of columns in the selection list of two SELECT statements must be the same, and the data types of the columns in the corresponding position must be the same or compatible, and the order of the columns in each SELECT statement must be the same.

The column name in the 2.UNION result set is always equal to the column name in the first SELECT statement in UNION

3.union automatically removes duplicate lines by default

4. If manual sorting is required, the order by statement must be added after the last select statement, but the sort column name he uses must be the column name in the first select select list

SELECT Age,Name FROM MyStudentInfoUNIONSELECT Id,GradeName FROM GradeInfoORDER BY Age DESC

UNION ALL

1. With the addition of the ALL keyword, the function is not to delete duplicate rows or to automatically sort rows

two。 If you want to merge fields of different data types into queries, you must convert them to make the corresponding data types the same or compatible

3. When two tables are merged, the number of columns is different. As long as you add columns to one of the table sources, you can make the number of columns in both tables the same.

SELECT Age,Name,Address FROM MyStudentInfoUNION ALLSELECT Id,GradeName,NULL FROM GradeInfoORDER BY Age DESC

What is the difference between UNION and join query

1. In a merge, the number and data type of the two tables must be the same; in a join, the rows of one table may be very different from those of the other

two。 In a merge, the number of rows is the sum of two table rows; in a join, the maximum number of rows is their product.

Thank you for reading this article carefully. I hope the article "how to achieve join and merge query in SQL SERVER" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.