Using Subqueries in SQL to Modify Data

8 Key excerpts on "Using Subqueries in SQL to Modify Data"

• 

  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 Databricks with Spark SQL

  Leverage SQL to query and analyze Big Data for insights (English Edition)

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

    (Publisher)

  HAPTER 12Subqueries Introduction

  In the previous eleven chapters of this book, you have learned the basics of SQL. From this chapter onwards, we will understand resolving some more complex analytical challenges. The first step on this path is to learn how to use independent SQL queries inside other queries. This technique is called using subqueries. It is particularly useful in data analysis as it allows you to include (among other things) aggregated data in detailed row sets or to compare a whole dataset with subsets of data.

  Structure In this chapter, we will cover the following topics:

  • Adding aggregated fields to detailed datasets
  • Displaying a value as the percentage of a total
  • Using a subquery to filter data
  • Using a subquery as part of a calculation to filter data
  • Filtering on an aggregated range of data using multiple subqueries
  • Filtering on aggregated output using a second aggregation
  • Using multiple results from a subquery to filter data
  • Complex aggregated subqueries
  • Nested subqueries
  • Using subqueries to exclude data
  • Filtering across queries and subqueries
  • Applying separate filters to the subquery and the main query

  Objectives

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

  This chapter will help you to see how subqueries are not an abstract database concept but an analytical skill that helps in digging deep into data and unearthing the insights that keep the user ahead of the competition. In this chapter, you learn how to:

  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

  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

  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

  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

  PROC SQL

  Beyond the Basics Using SAS, Third Edition

  • Kirk Paul Lafler(Author)
  • 2019(Publication Date)
  • SAS Institute

    (Publisher)

  When processed, the inner query passes a Boolean value to the outer query consisting of either True if it returns a minimum of one row or False if no rows are returned by the subquery. The results of the inner query are stored in a temporary results table and used as input to the main query. Our exploration of subqueries will involve using them with comparison operators, the IN predicate, and the ANY and ALL keywords, and will conclude with a look at a special type of subquery called a correlated subquery. Alternate Approaches to Subqueries A subquery is a very useful construct, especially when information from multiple tables needs to be interrelated. Unfortunately, a subquery is not always easy to construct and might be even more difficult to understand. So before constructing every table relation with a subquery, consider your options carefully. When all of the information is available in a single table, a simple query is probably all that needs to be constructed. Suppose that you want to produce a report that consists of the invoice information for Global Comm Corp. Let’s further assume that you know the specific manufacturer number for Global Comm Corp. Knowing this means that you don’t have to go into the MANUFACTURERS table to find it. In the next example, a simple query is constructed to retrieve all of the invoice information from the INVOICE table. Simple Query PROC SQL; SELECT * FROM INVOICE WHERE manunum = 210; QUIT; Results But, what if all the information is not in a single table? And what if the manufacturer number for Global Comm Corp is not known? As shown previously, a join can be constructed just as easily as a subquery. Some users prefer joins to subqueries because joins can be easier to understand and maintain. In fact, a join frequently performs better than a subquery. In the next example, the manufacturer number for Global Comm Corp is not known

  Sign up to read

  Learn more about book