Using Subqueries in SQL Predicates

8 Key excerpts on "Using Subqueries in SQL Predicates"

• 

  eBook - ePub

  Querying MariaDB

  Use SQL Operations, Data Extraction, and Custom Queries to Make your MariaDB Database Analytics more Accessible (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  C HAPTER 12 Subqueries Now that you have mastered the basics of SQL, it is time to move on and start resolving some more complex analytical challenges. The first step on this path is to learn how you can use independent SQL queries inside other queries. This technique is called using subqueries. It is particularly useful in data analysis as it allows you (among other things) to include aggregated data in detailed rowsets, for instance, or to compare a whole dataset with subsets of data. What Are Subqueries? Subqueries are more of a new technique than a new set of SQL keywords. In fact, you can write subqueries using only the SQL knowledge that you have acquired so far in this book. Nonetheless, learning to use subqueries to solve data challenges is a key part of your SQL apprenticeship. Once you master the art of the subquery, you are able to produce clear, concise, and in-depth analytics quickly and efficiently. I want to convince you that subqueries are not an abstract database concept but an analytical skill that helps you dig deep into your data and unearth the insights that keep you ahead of the competition. In this chapter you learn how to Compare categories of data to the whole dataset and calculate percentages Isolate top and bottom elements in a dataset and use them as a basis for filtering records Filter data by comparing records to averages and overall thresholds These—and many more analytical solutions—are made possible through the application of subqueries in your SQL. Let’s now move on to some real-world examples that illustrate how subqueries can turn raw data into meaningful information. 1. Adding Aggregated Fields to Detailed Data Sets Data analysis often means comparing individual elements to either all or a part of a dataset. As a first step, suppose that you want to count the number of cars sold per country and that you also want to see the total car sales for all vehicles, whatever their country, in the same query

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying MySQL

  Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  HAPTER 12

  Subqueries

  Now that you have mastered the basics of SQL, it is time to move on and start resolving some more complex analytical challenges. The first step on this path is to learn how you can use independent SQL queries inside other queries. This technique is called using subqueries . It is particularly useful in data analysis as it allows you (among other things) to include aggregated data in detailed rowsets, for instance, or to compare a whole dataset with subsets of data.

  What Are Subqueries?

  Subqueries are more of a new technique than a new set of SQL keywords. In fact, you can write subqueries using only the SQL knowledge that you have acquired so far. Nonetheless, learning to use subqueries to solve data challenges is a key part of your SQL apprenticeship. Once you master the art of the subquery, you are able to produce clear, concise, and in-depth analytics quickly and efficiently.

  I want to convince you that subqueries are not an abstract database concept but an analytical skill that helps you dig deep into your data and unearth the insights that keep you ahead of the competition. In this chapter you learn how to

  • Compare categories of data to the whole dataset and calculate percentages
  • Isolate top and bottom elements in a dataset and use them as a basis for filtering records
  • Filter data by comparing records to averages and overall thresholds

  These—and many more analytical solutions—are made possible through the application of subqueries in your SQL. Let’s now move on to some real-world examples that illustrate how subqueries can turn raw data into meaningful information.

  1. Adding Aggregated Fields to Detailed Data Sets

  Data analysis

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying SQL Server

  Run T-SQL operations, data extraction, data manipulation, and custom queries to deliver simplified analytics (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  HAPTER 12

  Subqueries

  I n the first eleven chapters of this book you have learned the basics of SQL. Now it is time to move on and start resolving some more complex analytical challenges. The first step on this path is to learn how you can use independent SQL queries inside other queries. This technique is called using subqueries. It is particularly useful in data analysis as it allows you (among other things) to include aggregated data in detailed rowsets, for instance, or to compare a whole dataset with subsets of data.

  What Are Subqueries?

  Subqueries are more of a new technique than a new set of SQL keywords. In fact, you can write subqueries using only the SQL knowledge that you have acquired in the previous chapters. Nonetheless, learning to use subqueries to solve data challenges is a key part of your SQL apprenticeship. Once you master the art of the subquery, you are able to produce clear, concise, and in-depth analytics quickly and efficiently.

  I want to convince you that subqueries are not an abstract database concept but an analytical skill that helps you dig deep into your data and unearth the insights that keep you ahead of the competition. In this chapter you learn how to

  • Compare categories of data to the whole dataset and calculate percentages
  • Isolate top and bottom elements in a dataset and use them as a basis for filtering records
  • Filter data by comparing records to averages and overall thresholds

  These—and many more analytical solutions—are made possible through the application of subqueries in your SQL. Let’s now move on to some real-world examples that illustrate how subqueries can turn raw data into meaningful information.

  12.1 Adding Aggregated Fields to Detailed Data Sets

  Data analysis often means comparing individual elements to either all or a part of a dataset. As a first step, suppose that you want to count the number of cars sold per country and that you also want to see the total car sales for all vehicles, whatever their country, in the same query. Here is the SQL that lets you do this:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Analytic SQL in SQL Server 2014/2016

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

    (Publisher)

  SELECT P.BusinessEntityID, P.FirstName, P.LastName

   FROM Person. Person AS P

   ORDER BY P.BusinessEntityID

   OFFSET @NbrLineByPage ROWS

   FETCH NEXT @NbrLineByPage ROWS ONLY

   SET @Counter = @Counter +1;

  End ; GO

  2.3.5. Conclusion

  Pagination of results is a feature that is in high demand. The SQL 2011 standard has developed the necessary mechanisms to ensure this functionality through the DBMS according to the Database-First approach, thus freeing developers from additional pagination work that is generally difficult for them and not optimized for the end-user.

  2.4. Subqueries

  2.4.1. Introduction

  Modularity in procedural programming is a practical approach to solving complex problems by subdividing them into elementary, easily manageable sub-problems. This strategy is also valid in SQL language through a very efficient and effective mechanism, namely the subquery technique.

  Subqueries are nested queries, i.e. queries within queries. The results of an internal query are transmitted to the external query as intermediate values used in expressions of the latter to generate the final result.

  The subquery technique can be used in the different clauses of the main (external) request. In this section, we study the use of subqueries in the WHERE clause.

  2.4.2. Mind map of the fourth section

  This fourth section will be devoted to the subquery technique with its different variants.

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

  Figure 2.6.

  Mental map of the fourth section

  2.4.3. Autonomous subqueries

  Autonomous subqueries are solutions to respond to requests by writing queries that themselves require values to be determined so that they can be executed. These values, instead of being provided in the main or external query, will be replaced by internal queries to calculate them.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  In this chapter, you’ll use this concept of data fit to ask what amounts to multiple questions in just one query. Essentially, you’re going to look at ways of taking what seems like multiple queries and placing them into something that will execute as a complete unit. In addition, you’ll also be looking at query performance and what you can do to get the most out of queries.

  You’ll see how, using subqueries, you can make the seemingly impossible completely possible, and how an odd tweak here and there can make a big difference in your query performance.

  WHAT IS A SUBQUERY?

  A subquery is a normal T-SQL query that is nested inside another query. Subqueries are created using parentheses when you have a SELECT statement that serves as the basis for either part of the data or the condition in another query.

  Subqueries are generally used to fill one of a few needs:

  • To break a query into a series of logical steps
  • To provide a listing to be the target of a WHERE clause together with [IN|EXISTS|ANY|ALL ]
  • To provide a lookup driven by each individual record in a parent query

  Some subqueries are very easy to think of and build, but some are extremely complex — it usually depends on the complexity of the relationship between the inner (the sub) and the outer (the top) queries.

  It’s also worth noting that most subqueries (but definitely not all) can also be written using a join. In places where you can use a join instead, the join is usually the preferable choice for a variety of reasons that you will continue to explore over the remainder of the book.

  NOTE I have seen spirited debates among experienced developers over the joins versus subqueries (or CTEs, as you’ll see later in this chapter) issue.

  In general, the common wisdom has said to always use the join, and that’s what I’ve generally advocated (because of experience rather than traditional logic — you’ve already seen several places in this book where I’ve pointed out how traditional thinking can be bogus). In battling some thorny performance issues over the last year, I’ve had the opportunity to explore this a fair amount.

  What I’ve found was essentially that the right answer depends entirely upon circumstances. You will explore these circumstances fully toward the end of the chapter after you have a bit more background.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Microsoft Business Intelligence Tools for Excel Analysts

  • Michael Alexander, Jared Decker, Bernard Wehbe(Authors)
  • 2014(Publication Date)
  • Wiley

    (Publisher)

  Like operator stems from the use of wildcard characters. SQL Server includes four wildcard characters.

  See Table 9-1 for how to use wildcards.

  Subqueries

  Just as the name implies, a subquery is a query within a query. It can be nested anywhere an SQL expression can be used. In most cases, you can accomplish your objective without the use of subqueries, but they tend to be a style preference. In the following sections, we show you when you'd use a subquery.

  In a From clause

  The subquery in this example returns all the records that exist both in FactFinance and DimScenario .

  Select   da.AccountType, sub.ScenarioName,sum(sub.Amount)From   dbo.DimAccount da      Left Join (Select   ff.Amount, ff.AccountKey,ds.ScenarioName         From   dbo.FactFinance ff            Join dbo.DimScenario ds               On ff.ScenarioKey=ds.ScenarioKey         ) sub         On da.AccountKey=sub.AccountKeyGroup By   da.AccountType, sub.ScenarioName

  The result set is left joined to DimAccount . The final output returns all rows from DimAccount and only those rows that match from the subquery.

  Correlated subqueries

  A correlated subquery means that the subquery runs once for every row in the main query. You can put correlated subqueries in several places inside a Select statement. This subquery runs once for every row in FactFinance and is restricted by the relationship on ScenarioKey with the outer query.

  Select   Distinct      ff.AccountKey,   (Select   ds.ScenarioNameFrom   dbo.DimScenario dsWhere   ff.ScenarioKey=ds.ScenarioKey)From   dbo.FactFinance ff

  This query can be much more simply achieved by putting DimScenario in the From clause and creating an inner join on ScenarioKey . The output would be exactly the same. Always avoid making your SQL more complicated whenever possible.

  Advanced joins

  When you start working in SQL, basic joins work fine for you. However, as you start getting into advanced SQL and sophisticated analytics requirements, you may find you need to go beyond the basics to accomplish your objectives. Depending on your situation, you may need to use one or more of the advanced joins described in this section.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Relational Database Design and Implementation

  • Jan L. Harrington(Author)
  • 2016(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  2

  A subquery (or subselect ) is a complete SELECT statement embedded within another SELECT. The result of the inner SELECT becomes data used by the outer.

  Note: Subqueries have other uses besides avoiding joins, which you will see throughout the rest of this book. A query containing a subquery has the following general form:

  There are two general types of subqueries. In an uncorrelated subquery , the SQL command processor is able to complete the processing of the inner SELECT before moving to the outer. However, in a correlated subquery , the SQL command processor cannot complete the inner query without information from the outer. Correlated subqueries usually require that the inner SELECT be performed more than once and therefore can execute relatively slowly. The same is not true for uncorrelated subqueries which can be used to replace join syntax and therefore may produce faster performance.

  Note: You will see examples of correlated subqueries beginning in Chapter 18 .

  Using the IN Operator

  As a first example, consider the following query

  which produces the following output:

  When looking at the preceding output, don’t forget that by default the DBMS adds the time to the display of a date column. In this case, there is no time included in the stored data, so the time appears as all zeros.

  We can rewrite the query using subquery syntax as

  The inner SELECT retrieves data from the volume table, and produces a set of sale IDs. The outer SELECT then retrieves data from sale where the sale ID is in the set of values retrieved by the subquery.

  The use of the IN operator is actually exactly the same as the use you read about in Chapter 16

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Management Systems

  • Sotirios Zygiaris(Author)
  • 2018(Publication Date)
  • Emerald Publishing Limited

    (Publisher)

  parenthesis. If parentheses are omitted, an error will be generated by the SQL engine.

  • (SELECT MAX(SLPRS_SALARY) FROM SLPRS);

  Figure 6.2. Querying for Max Salary and ID with Subquery.

  Then the outer query is executed finding the row WHERE the SLPRS_ID = 436652. In the case where more than one salesperson has as SLPRS_SALARY the value of 436652, multiple selected rows will be returned by the query.

  • SELECT SLPRS_ID, SLPRS_SALARY FROM SLPRS WHERE SLPRS_SALARY = 

  A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

  Subqueries, as inner queries, may be categorized by the number of rows they return to the outer query.

  6.2.2. WHERE Subqueries

  When a single value (field) and not a row is returned by the subquery, then the use of the WHERE clause is appropriate. In the WHERE clause, all conditional operators <, >, <=, >=, or = may be applied, for example:

  What are the products (ID, Description, QOH) with quantity on hand having more than the average quantity on hand?

  • SELECT PROD_ID, PROD_DESCR, PROD_QOH FROM PRODUCT
  • WHERE PROD_QOH > (SELECT AVG(PROD_QOH) FROM PRODUCT);

  First, the inner query (SELECT AVG(PROD_QOH) FROM PRODUCT) is executed that returns a single value as AVG(PROD_QOH). Then the outer query compares each row of product for PROD_QOH that is greater than the returned average value by the inner query.

  Sign up to read

  Learn more about book