SQL Subquery

7 Key excerpts on "SQL Subquery"

• 

  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)

  In this chapter, you saw how you can nest queries inside queries to deliver far-reaching analytics. As you have learned, a subquery is nothing more than a standard SQL query in its own right. However, the ability to return two or more independent datasets inside a single query opens up the road to much more advanced comparative analysis of your data. You can compare data at different levels of aggregation and calculate percentages of a total with a minimum of effort. Subqueries also let you isolate data in many subtle and surprising ways and can, consequently, help you deliver deeper insights into your data.

  Core Knowledge Learned in This Chapter

  The concepts and techniques that you have seen in this chapter are

  • A subquery is completely independent of the outer query.
  • You can refer to the subquery in the SELECT, WHERE, or HAVING clauses of the outer query.
  • You must enclose a subquery in parenthesis.
  • A subquery must include a SELECT clause and a FROM clause.
  • A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
  • You can use subqueries to include or exclude data.
  • If a subquery returns more than one record, then you have to use the IN operator in the WHERE or HAVING clause of the outer query.
  • You can nest subqueries inside other subqueries.

  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

  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

  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

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Advanced Querying Techniques

  In this chapter, you will learn how to use advanced querying techniques and how to use two different kinds of subqueries, correlated and non-correlated. Then, you will learn about two different types of common table expressions, recursive and non-recursive. Next, you will learn about query hints and how to choose which index your query will use. Finally, you will learn about isolation levels and concepts related to how data is read from, and written to, tables.

  In this chapter, we will cover the following topics:

  • Using subqueries
  • Using common table expressions
  • Using query hints and transaction isolation levels

  Let's get started!

  Passage contains an image

  Technical requirements

  You can refer to the code files for this chapter by going to the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-11

  Passage contains an image

  Using subqueries

  A subquery is a query nested in another query with parentheses. A subquery can be used in SELECT, FROM, INSERT, DELETE, UPDATE, and WHERE clauses, and can also be nested inside another subquery. Subqueries can be beneficial when a query may require complex joins and unions. A subquery can return either a single value, row, column, or table.

  A subquery is the inner query of another query, which is considered the outer query. The inner query is executed before the outer query so that the inner query results are passed to the outer query. For example, in the following code, the query inside parentheses is the inner query, while the query outside parentheses is the outer query:

  SELECT col1FROM table1 WHERE col1 IN (SELECT col1 FROM table 2 WHERE col1 = 'test')

  Passage contains an image

  Understanding the different types of subqueries and their usage

  There are two types of subqueries, correlated and non-correlated. The following table outlines their differences:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

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

    (Publisher)

  Self-Join. A self join joins a table to itself and is commonly used with a table containing a hierarchy of rows such as a family tree, which is in effect a denormalized one-to-many relationship. A selfjoin does not require any explicit syntax other than including the same table in the FROM clause twice, as in the following example:

  SELECT P.NAME AS PARENT, C.NAME FROM SUBJECT P JOIN SUBJECT C ON (C.PARENT_ID = P.SUBJECT_ID);

  PARENT	NAME
  --------------------------------	-----------------
  Fiction	Literature
  Non-Fiction	Self Help
  Non-Fiction	Esoteric
  Non-Fiction	Metaphysics
  Fiction	Science Fiction
  Non-Fiction	Technical
  Technical	Computers
  Fiction	Fantasy
  Fiction	Drama
  Fiction	Whodunnit
  Fiction	Suspense
  Fiction	Poetry
  Literature	Shakespearian
  Literature	Modern American
  Literature	19th Century American
  Literature	Victorian

  Some databases do have specific syntax for performing self-joins on hierarchical data contained in a single table.

  4.2.5    Nested Queries

  A nested query is a query containing one or more subqueries, where nested means that a query can be nested within a query, within another query, and so on. Some databases use the IN set operator to nest queries where one value is checked for membership in a list of values found from a subquery as in the following example:

  SELECT * FROM AUTHOR WHERE AUTHOR_ID IN (SELECT AUTHOR_ID FROM PUBLICATION WHERE PUBLICATION_ID IN   (SELECT PUBLICATION_ID FROM EDITION WHERE PUBLISHER_ID IN    (SELECT PUBLISHER_ID FROM PUBLISHER)));

  Some databases allow you to pass a cross checking or correlation value into a subquery, as in the case below, usually an index:

  SELECT * FROM AUTHOR WHERE AUTHOR_ID IN (SELECT AUTHOR_ID FROM PUBLICATION WHERE AUTHOR_ID = AUTHOR. AUTHOR_ID);

  Sign up to read

  Learn more about book