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 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

  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

  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 - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  If it’s one or less, then that part of the WHERE clause evaluates to true. Note that the Location table of the DELETE statement and the MemberDetails tables of the subquery have been joined on the State and City fields: Location.State = MemberDetails.State This is absolutely vital, otherwise the subquery returns more than one result. The second condition in the WHERE clause specifies that LocationId must not be in the Attendance table. Because an AND clause joins the two conditions in the WHERE clause, both conditions must be true. If you execute the query, you find that no records match both conditions, and therefore no records are deleted from the Location table. 260 Chapter 8 Summary This chapter covered the nesting of a query inside another query, a powerful tool that you can use when faced with tricky data extraction questions. Specifically, this chapter covered the following topics: ❑ A subquery is just like a normal query, with SELECT clauses, WHERE statements, and GROUP BY and HAVING clauses. ❑ Subqueries can be included in a SELECT statement’s column listing, or they can be used inside a WHERE statement to enable results to be filtered. ❑ Aggregate functions are commonly used with subqueries because a subquery must normally return just one record for each record in the main query. ❑ A subquery can return multiple rows when used with an IN, ANY, ALL, or EXISTS operator. These operators function as follows: ❑ The IN operator is used to find out whether a value is in one of the values returned by a subquery. ❑ The ANY operator allows a comparison between a value and any of the values returned by a subquery. Comparisons are not limited to just equals (=); rather, you can use any comparison operator, such as greater than (>), less than (<), and so on. ❑ The ALL operator requires that every item in the list, or all the results of a subquery, comply with the condition set by the comparison operator used with ALL.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  A Guide to SQL

  • Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
  • 2020(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  M O D U L E 4 SINGLE-TABLE QUERIES O B J E C T I V E S • Retrieve data from a database using SQL commands. • Use simple and compound conditions in queries. • Use the BETWEEN, LIKE, and IN operators in queries. • Use computed columns in queries. • Sort data using the ORDER BY clause. • Sort data using multiple keys and in ascending and descending order. • Use aggregate functions in a query. • Use subqueries. • Group data using the GROUP BY clause. • Select individual groups of data using the HAVING clause. • Retrieve columns with null values. INTRODUCTION In this module, you learn about the SQL SELECT command that is used to retrieve data in a database. You examine ways to sort data and use SQL functions to count rows and calculate totals. You also learn about a special feature of SQL that lets you nest SELECT commands by placing one SELECT command inside another. Finally, you learn how to group rows that have matching values in a column. CONSTRUCTING SIMPLE QUERIES One of the most important features of a DBMS is its ability to answer a wide variety of questions concerning the data in a database. When you need to find data that answers a specific question, you use a query. A query is a question represented in a way that the DBMS can understand. In SQL, you use the SELECT command to query a database. The basic form of the SELECT command is SELECT-FROM-WHERE. After you type the word SELECT, you list the columns that you want to include in the query results. This portion of the command is called the SELECT clause. Next, you type the word FROM followed by the name of the Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  We’re going to limit ourselves to the easiest type of subquery to understand, where the nested (inner) query is used to retrieve a value that is passed up to the outer query and used as input to the SELECT statement. This is known as a noncorrelated subquery, because the inner query does not depend on the outer query. 7.2.3 USING BASIC SUBQUERIES 247 There is another type of query, known as a correlated subquery, where the inner query depends on values passed to it by the outer query. This is a more advanced type of subquery and is beyond the scope of this chapter’s discussion. In a noncorrelated subquery, the nested query is used in the qualifying logic in the WHERE clause. When you have multiple layers of nesting, the execution rule is that the innermost SELECT statement is executed first, and its results are then provided as input to the SELECT statement at the next level up. This pro- cedure can be used as an alternative to the join. Consider the following query: Find the name of the salesperson responsible for Customer Number 1525. You’ve already seen how you can do this as a join. When you have the option of using either a join or a subquery, you should usually use the join because it typically requires fewer resources to resolve. However, let’s look at resolving this as a subquery to illustrate how a subquery works. If you methodically weave through the database tables to solve this query, you start at the CUSTOMER table, find the record for Customer Number 1525, and discover in that record that the salesperson responsible for this customer is Salesperson Number 361. You then take that information to the SALESPERSON table where you look up the record for Salesperson Number 361 and discover in it that the salesperson’s name is Carlyle.

  Sign up to read

  Learn more about book