Analytic SQL in SQL Server 2014/2016

2Queries

2.1. Data filtering

2.1.1. Introduction

The notion of filters is essential in SQL language. They consist of restricting the results to be displayed from a database. These filters are provided by different techniques incorporated into the query, from the simple WHERE clause to the use of the full-text search engine.

2.1.2. Mind map of the first section

This first section will be devoted to data filters.

Figure 2.1 presents the mind map of the different concepts that will be covered in this section.

2.1.3. Types of filters

Using filters in an SQL query is like doing a conditional search.

Figure 2.1. Mind map of the first section

These searches can be divided into different categories as follows:

• – exact conditional search, which consists of returning a dataset fulfilling the condition(s) used in the filter. This search is invoked by the WHERE clause. The exact conditional search can itself be divided into two subcategories:

  • - exact conditional search with a filter by condition: this type of search consists of generating a particular dataset using one or more specific conditions;

  • - exact conditional search with a pattern filter: this type of search consists of generating a particular dataset using a pattern instead of one or more specific conditions. The search by pattern can itself be divided into two subcategories:

    • - exact conditional search with a filter ...