SQL Server group query and connection query

Group query using GROUP BY

Multi column grouping query

Group filtering using HAVING clause

Classification of multi table join queries

External connection query

1, Group query using GROUP BY

The above type of query is called group query in SQL Server. Group query is implemented by GROUP BY clause.

Group by my SubjectId and display the average score of SudentResult in each SubjectId.

SELECT SubjectId,AVG(SudentResult) as Average score of all courses 
FROM Result
GROUP BY SubjectId;

Note: if grouping query is required, it must be used with aggregate functions such as AVG(), otherwise an error will be reported.

Let's take another example

Query the total number of male and female students, and use the aggregate function COUNT().

First group by gender column: GROUP BY Sex.

Secondly, count the total number of each group, using the aggregate function COUNT().

The T-SQL statement is as follows:

SELECT COUNT(*) AS Number of people,Sex FROM Student1
GROUP BY Sex;

2, Multi column grouping query

For example, to count the number of male and female students in each semester, theoretically separate each semester, and then count the number of male and female students for each semester, that is, they need to be grouped according to two columns: grade and gender.

The T-SQL statement is as follows:

SELECT COUNT(*) AS Number of people,Gradeld AS grade,Sex AS Gender FROM Student1
GROUP BY Gradeld,Sex
ORDER BY Gradeld;

3, Group filtering using HAVING clause

For example, to query the total number of students in grade more than grade 15.

First, the total number of people in each grade can be obtained by grouping query. The corresponding T-SQL statement is as follows.

SELECT COUNT(*) AS Number of people,Gradeld AS grade 
FROM Student1
GROUP BY Gradeld
HAVING COUNT(*)>15;

4, Classification of multi table join queries

The following are several common connection query methods: internal connection and external connection

1. Internal connection query

Inner join query is the most typical and commonly used join query. It matches according to the common columns in the table. In particular, inner join queries are usually used when there is a primary foreign key relationship between two tables.

2. External connection query

An external join query is all the records in at least one table and selectively returns the records of another table according to the matching criteria.

The external connection can be left external connection or right external connection.

Internal connection query

Internal connection query usually uses comparison operators such as "=" or "< >" to judge whether the values of the two columns are equal. The above-mentioned connection to judge the student name according to the student ID is an internal connection.

INNER JOIN uses the INNER JOIN... ON keyword or the WHERE clause to associate tables. INNER JOIN query can be implemented in two ways.

1. Specify the connection condition in the WHERE clause

For example, the T-SQL for querying student number, student name, examination subject and score is as follows.

select s.StudentNo ,s.StudentName,k.SubjectName,r.SudentResult
from Student1 as s,Result as r,Subject as k
where s.StudentNo = r.StudentNo and r.SubjectId = k.SubjectId
order by StudentNo;

This is the connection query of our student table Student1, chart of subjects Subject and Result table.

2. Use INNER JOIN... ON in the FROM clause

The above query can also be implemented through the following INNER JOIN... ON clause.

select s.StudentNo ,s.StudentName,k.SubjectName,r.SudentResult from Student1 as s
inner join Result as r on s.StudentNo = r.StudentNo
inner join Subject as k on r.SubjectId = k.SubjectId
order by StudentNo; 

5, External connection query

External connection query includes left external connection query and right external connection query

1. Left outer connection query

The result of the left outer join query includes all the rows of the left table specified in the LEET JOIN clause, not just the rows matched by the join column. If a row of the left table has no matching row in the right table, all the selected columns of the right table in the associated result set row are null.

The LEFT OUTER JOIN query uses the LEFT JOIN... ON or LEFT OUTER JOIN... ON keywords to associate tables. For example, to count the test scores of all students, it is required to display the test scores of all students participating in the test, and the students not participating in the test should also be displayed. At this time, the T-SQL statement with student information table as the main table (sometimes called the left table) and student grade table as the connection query from the left outside of the table is as follows:

select s.StudentNo ,s.StudentName,k.SubjectName,r.SudentResult from Student1 as s
left join Result as r on s.StudentNo = r.StudentNo
left join Subject  as k on k.Gradeid = s.Gradeld 
order by StudentNo;

2. Right external connection query

The right outer join query is similar to the left outer link query, except that it should include all matching rows in the right table. If some items in the right table have no corresponding items in the left table, they are filled with NULL values.

The RIGHT OUTER JOIN query uses the RIGHT JOIN... ON or RIGHT OUTER JOIN... ON keywords to associate tables. For example, in a database, the right outer connection between book table Titles and publisher table Publishers will include all Publishers, and Publishers without book Titles in table Titles (haven't published a book for a long time) will also be listed.

SELECT Titles.Title_id,Titles.Title,Publishers.Pub_name
FROM titles
RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id;

difference:

1. The result of the left outer join query includes all rows of the left table specified in the LEET JOIN clause, not just the rows matched by the join column. If a row of the left table has no matching row in the right table, all the selected columns of the right table in the associated result set row are null.

2. The right outer link query is similar to the left outer link query, except that it should include all matching rows in the right table. If some items in the right table have no corresponding items in the left table, they are filled with NULL values (the item is not displayed in the virtual table).

--1.Left lateral connection
select s.StudentNo ,s.StudentName,r.SudentResult from Student1 as s
left join Result as r on s.StudentNo = r.StudentNo
order by StudentNo;

--2.Right external connection
select s.StudentNo ,s.StudentName,r.SudentResult from Student1 as s
right join Result as r on s.StudentNo = r.StudentNo
order by StudentNo;