SQL WHERE

4 Key excerpts on "SQL WHERE"

• 

  eBook - ePub

  Analytic SQL in SQL Server 2014/2016

  • Riadh Ghlala(Author)
  • 2019(Publication Date)
  • Wiley-ISTE

    (Publisher)

  2 Queries

  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 by pattern using the LIKE operator;
      • - exact conditional search with a filter by pattern using the REGEXP function;
  • – approximate and semantic conditional search:
    • - Full-Text Search: complex queries on character data on SQL tables can be performed using full-text queries on SQL and Azure SQL databases. SQL Server Full-Text Search is an easy to use, very fast and extensible solution for indexing and searching over different types of document content (Word, Excel, PDF and HTML);

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • Auerbach Publications

    (Publisher)

  What does all this mean without using a plethora of nasty long words? In short, SQL was developed as a shorthand method of retrieving information from relational databases, and SQL has become the industry standard over the last 20 years. Here is an example of a query (a question posed to the database that asks for specific information), which is written in SQL and retrieves all the rows in a table called AUTHOR:

  SELECT AUTHOR_ID,  NAME FROM AUTHOR;

  4.1.2    SQL for Different Databases

  SQL as implemented in different database vendor products is usually standardized using American National Standards Institute (ANSI) standardized SQL, but individual database vendors will have different naming conventions, different syntax, and even extra add-on bells and whistles. In reality, different database vendors develop a unique relational database and Relational Database Management Systems (RDBMS) containing a proprietary form of SQL.

  Relational Database Management System (RDBMS) is a term used to describe both a database and the associated tools (database management toolkit) used to work with and manage database software.

  So what are the basics of SQL?

  4.1.3    Introducing SQL

  This section summarizes the different types of SQL as described in the following list:

  •  Query Command. Querying or reading a database is performed with a single command called the SELECT command, which is used to retrieve data from tables in a database. There are various ways in which data can be retrieved from tables:

  ○  Basic Query. Retrieve all rows from a single table.

  ○  Filtered Query. A filtered query uses a WHERE clause to include or exclude specific rows.

  ○  Sorted Query. Sorting uses the ORDER BY clause to retrieve rows in a specific sorted order.

  ○  Aggregated Query

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Data Engineering with dbt

  A practical guide to building a cloud-based, pragmatic, and dependable data platform with SQL

  • Roberto Zagni(Author)
  • 2023(Publication Date)
  • Packt Publishing

    (Publisher)

  to 20. Important note

  If no ORDER BY clause is present, the order of the rows is undefined and could differ in each execution of the same query. In this case, the result of a LIMIT clause is non-deterministic because what rows are returned depends on the order in which the rows happen in the result set.

  Query clause order of evaluation

  In the previous sections, we have seen all the clauses that can appear in a SELECT statement.

  Now is a good time to bring your attention to the fact that these clauses are generally evaluated in the following specific order, as well as what it is important to pay attention to for each clause:

  1. FROM and its JOIN subclause, which are used to identify the source data for the query.
  2. The WHERE clause, which is used to filter out the source data that we do not want.

  This is probably the most important clause for performance, because the less data a query works on, the quicker it is. Use WHERE whenever possible to just bring in the data you need.

  1. The GROUP BY clause, which groups the source data left after applying the WHERE clause and calculates the aggregate functions on the grouped data.
  2. The HAVING clause, which filters on the results of GROUP BY .
  3. Partitioning of the windows and calculation of the window functions .
  4. The QUALIFY clause, which filters on the results of the window functions.
  5. The DISTINCT keyword, if applied to the SELECT clause, which removes duplicated rows.
  6. The ORDER BY clause, which puts the resulting rows in the desired order.
  7. The LIMIT clause, which caps the rows returned by the query to the desired amount.

  SQL operators

  When writing queries, we can perform operations on the data handled by the query.

  We do so by building expressions that return the desired value, using functions and operators.

  We can perform an operation pretty much everywhere a value is expected: in the SELECT clause to provide the desired outputs by transforming the inputs, in the WHERE

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  logical_expression following WHERE may be arbitrarily complicated. For example:

  SELECT ProductName FROM PRODUCT WHERE UnitsOnOrder*UnitPrice > 5000;

  shows the name of each product for which the total value of inventory on order is over $5,000. In addition to simple comparisons using = (equal), > (greater than), < (less than), <= (less than or equal to), and > = (greater than or equal to), SQL allows compound logical expressions constructed through AND and OR operations. For example:

  SELECT ProductName, UnitsOnOrder, UnitPrice FROM PRODUCT WHERE UnitsOnOrder >= 100 OR UnitPrice < 50;

  displays the name, number of units on order, and unit price of each product that has at least 100 units on order or has a price less than $50. Note that OR in SQL is “inclusive,” as in most computer languages, so that a record meeting both sub‐conditions is considered to satisfy the OR condition. In the query above, for example, a product with at least 100 units on order and also costing less than $50 would be displayed in the query output. Here is another example of a compound condition:

  SELECT FirstName, LastName FROM CUSTOMER WHERE City="Hamilton" AND State="NJ";

  This query will display the names of all customers from Hamilton, NJ; the database also contains a customer from Hamilton, NY, but this customer is not included in the query result because the State field value does not match the condition. When comparing text fields to literal character strings such as “CA”, you should enclose the literal character strings in double quotes. Otherwise, SQL will try to interpret the character string as an attribute name.

  Inner Joins

  So far, this chapter has considered only queries drawn from a single table. We now discuss how SQL can express queries based on data from multiple tables. The most common technique for basing queries on multiple tables is called an inner join. An inner join consists of all combinations of rows selected from two tables that meet some matching condition, formally called a join predicate

  Sign up to read

  Learn more about book