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

  SQL: 1999

  Understanding Relational Language Components

  • Jim Melton, Alan R. Simon(Authors)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Example 9-13 Subquery Used in an IN Predicate SELECT title, dvds in stock u FROM movie titles m WHERE title IN ( SELECT movie title FROM movies stars WHERE actor last name = 'Gibson' D -.. AND actor first name = 'Mel' ) ; 9.10 Subqueries 319 Subqueries like these last two stand on their ownmthat is, they do not inter- act with the data being accumulated in the rest of the query. In one, we simply wanted to find the most expensive DVD currently being sold, without regard to its title or any other data with which the main query was concerned. In the other, our IN predicate requires the use of a subquery, but we had no need to correlate the outer query and the subquery in any way. But there are many situations where the information being pursued in a subquery is dependent in some way on data known to the outer query. For instance, let's examine another example taken from Chapter 7, Predicates, repeated in Example 9-14. Example 9-14 Subquery Used in an EXISTSPredicate SELECT title, tapes in stock m m FROM movie titles m WHERE EXISTS ( SELECT * FROM movies stars WHERE movie title = m.title AND actor last name = 'Gibson' AND actor first name = 'Mel' ) ; Like Example 9-13, Example 9-14 uses a subquery as a component of a predicate (an EXISTS predicate, in this case). In that subquery, the first predicate in the WHERE clause uses the column reference m. title. By looking at the FROM clause of the outer query, we see that M is actually a correlation name associated with the movi e_ti t 1es table being queried in that outer query. Because the information retrieved by the subquery depends on data being processed in the outer query, we say that the subquery is correlated with the outer query. More commonly, we say that this is a correlated subquery. Simply put, a cor- related subquery is one that uses data from an outer query.

  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

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

  SQL Clearly Explained

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

    (Publisher)

  However, there is a type of SQL syntax~a subquery--that you can use to obtain the same re- sult but avoid performing a join altogether. 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. Note: One of the major limitations of versions of MySQL prior to 4.1 (which had not been released at the time the second edi- tion of this book was prepared) is that they do not support sub- queries. If you happen to need a type of query that can be best performed using a subquery, then this is a significant problem. As we discuss subqueries, you will encounter both text and notes that show alternative syntaxes that you can use with MySQL until the 4.1 release is available. A query containing a subquery has the following general form: SELECT column(s) FROM table WHERE operator (SELECT column (s)) FROM table WHERE ... ) There are two general types of subqueries. In an uncorrelated subque- ry, the SQL command processor is able to complete the processing of the inner SELECT before moving to the outer. However, in a corre- lated subquery, the SQL command processor cannot complete the in- ner 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 AVOIDING JOINS WITH UNCORRELATEDSUBQUERIES 95 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 6. Conformance note: Subqueries are part of the SQL-86 standard and therefore are supported by most SQL DBMSs. They are re- quired for conformance to the Core SQL:1999 standard.

  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