T-SQL query advanced-calculation between datasets

Overview

The origins of relational databases originate from the set concept in mathematics. therefore, the operation between a set and a set also inherits the operation between mathematical sets. in relational databases, it is often used for "relational" in two relational databases that are not directly used, such as foreign keys. however, there is an indirect relationship between the two datasets. For example, there is an indirect relationship between the participants in the two competitions.

 

Types of dataset operations

In the T-SQL, relational operations can be divided into four types, first look at the example of the table:

The example table here shows the records of participants in two different meetings, meeting1 and meeting2, respectively, as shown below:

Relational operations can be divided into the following four types:

1. A between B is not only in a but also in B.

In the instance table, the actual example is a set of participants who attended both the first meeting and the second meeting, for example:

 

 

2. A then B. The requested data is either in dataset A or in dataset B.

In the instance table, the actual example is to participate in the first meeting or a set of participants in the Second Meeting, such:

 

3. A-B, that is, the requested data is in dataset A, not in dataset B

In the instance table, the actual example is the set of persons who attended the first meeting and did not attend the Second Meeting, for example:

 

4. B-A, where both the requested data is in dataset B and not in dataset

This is actually no essential difference from the third case above, but the order is reversed, for example:

 

Dataset Source

In the T-SQL, the two datasets involved in the dataset operation can come from any expression that returns the dataset. for example, a table, a subset of a table, multiple tables, temporary table variables, virtual columns, or even a scalar value

 

Dataset Operation Conditions

Not all datasets can be computed. Just like an apple + a pear cannot be equal to 2, the computation between datasets in the T-SQL needs to meet the following three conditions:

1. The two datasets must have the same number of columns (column)

2. The order of the columns between the two datasets must be consistent.

3. The data type of each corresponding column between the two datasets must match

 

Implementation of dataset calculation in T-SQL

1. A minus B is implemented using Union

In the T-SQL, union is provided to realize the operation of A minus B. In fact, Union has two versions:

Union

Union indicates the relationship between A and B. When two data sets have the same row, the only one is retained:

 

Union all

Union all also implements the logic of a distinct B, but unlike Union, when there are repeated rows in two datasets, they are all retained:

 

2. A using B, implemented using intersect

The T-SQL provides the Intersect keyword to implement the relationship between a between B:

 

3. A-B, which is implemented using ipvt

The T-SQL provides the limit t keyword to implement the relationship of A-B:

 

Alias and sorting of dataset operations

If no alias is specified for the data column, the name of the data column is calculated based on the column name corresponding to the first set:

After union:

If you want to customize the column name, you must specify an alias for the dataset that appears in the first place in the dataset operation:

 

Sort the calculated results

Sorting the results after an operation is very simple. You only need to add the order by clause at the end of the operation. However, you must note that:

1. Order by is the sorting of the results after the entire operation, not for a single Dataset

2. The field names sorted after order by are the field names or aliases of the first dataset.

 

 

Summary

In this paper, a simple set operation is introduced in detail, and the realization of a simple set operation in T-SQL is given. InArticleFinally, the alias rule and sorting rule of operation are introduced. It is helpful to know more clearly about T-SQL query to master the operation between sets.