SQL Subquery

10 Key excerpts on "SQL Subquery"

• 

  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)

  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

  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

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • CRC Press

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

  eBook - PDF

  Beginning SQL

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

    (Publisher)

  A subquery is useful where you want to compare against a value extracted from the database itself rather than a predetermined value. For exam- ple, you might be asked to create a list of cities where the average year of birth is later than the average for the membership as a whole. To do this, you could use a HAVING clause plus a subquery that finds out the average year of birth of members: SELECT City FROM MemberDetails GROUP BY City HAVING AVG(YEAR(DateOfBirth)) > (SELECT AVG(YEAR(DateOfBirth)) FROM MemberDetails); 252 Chapter 8 This is the same as the query just mentioned, except 1990 is replaced with a subquery that returns the average year of birth of all members, which happens to be 1965. The final results table is as follows: City Big City Dover New Town Orange Town Sometimes it’s necessary to refer to the outer query, or even a query nested inside a subquery. To do this, you must give aliases to the tables involved, which is the topic of the next section on correlated subqueries. Correlated Subquery A correlated subquery is a subquery that references the outer query. A correlation variable is an alias given to tables and used to reference those tables in the subquery. In Chapter 3 you learned how to give tables a correlation, or alias name. The following example demonstrates a correlated subquery. This example isn’t necessarily the only way to get the result, but it does demonstrate a nested subquery.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Fundamentals of Database Management Systems

  • Mark L. Gillenson(Author)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  The system can simply start at the top of each table or index, as the case may be, and move downwards, without ever having to move upwards. SUMMARY SQL has become the standard relational database management data definition and data manipulation language. Data retrieval in SQL is accomplished with the SELECT command. SELECT commands can be run in a direct query mode or embedded in higher-level language programs in an embedded mode. The SELECT command can be used to retrieve one or more rows of a table, one or more columns of a table, or particular columns of particular rows. There are built-in functions that can sum and average data, find the minimum and maximum values of a set of data, and count the number of rows that satisfy a condition. These built-in functions can also be applied to multiple subsets or groups of rows. The SELECT command can also integrate data by joining two or more tables. Subqueries can be developed for certain specific circumstances. There is a strategy for writing SQL commands successfully. Performance is an important issue in the retrieval of data from relational databases. All relational database management systems have a relational query optimizer, which is software that looks for a good way to solve each relational query presented to it. While the ways that these query optimizers work are considered trade secrets, there are several standard concepts and techniques that they generally incorporate. KEY TERMS Access path AND/OR Base table BETWEEN Built-in functions Comparisons Data definition language (DDL) Data manipulation language (DML) Declarative DISTINCT Embedded mode Filtering GROUP BY HAVING IN LIKE Merge-scan join ORDER BY Nested-loop join Procedural Query Relational query optimizer Search argument SELECT Structured Query Language (SQL) Subquery QUESTIONS 1. What are the four basic operations that can be performed on stored data? 2.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  SQL Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  NOT IN .

  By contrast, a non-correlated subquery is an independent query whose output is substituted into the main query. The inner query is always executed before the outer query in a non-correlated subquery.

  In both types of subqueries, the nested query returns a (possibly empty) set of values that is then processed by the outermost SQL query. Consequently, there can be a performance penalty because the subquery may be evaluated again for each row processed by the outer query, thereby increasing the execution time for the entire SQL statement.

  Now that you understand the different types of subqueries, the following SQL statement selects the day and the forecast for the day (or days) that have the maximum temperature by means of a correlated subquery:

  SELECT day, forecast FROM weather WHERE temper IN (   SELECT max(temper) FROM weather);

  As you can see in the preceding SQL statement, a subquery enables you to restrict the data that is queried by the main query. Although we have not discussed the MAX() function in SQL, the purpose of this function is intuitive: it returns the maximum value of an attribute of a table. Similarly, MIN() and AVG() return the minimum and average values, respectively, of an attribute of a table. These aggregate functions are discussed again in Chapter 4

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  5 SQL: An Introduction to Querying LEARNING OBJECTIVES • To understand the mathematical foundations of a declarative SQL query • To implement fundamental SQL query expressions • To write SQL statements using nested subqueries and set operators • To analyze data using SQL’s aggregation and grouping capabilities • To develop SQL query statements to handle null values This chapter introduces you to SQL – the industry-standard query language for asking questions over data stored in a relational database. SQL is a declarative query language, specifying the tables that contain the information to answer the question along with the conditions that must hold on the data with a list of attributes to display in the answer. In this chapter, you will learn how to write SQL queries to analyze data. 5.1 Foundations Although there are always slight variations of the dialect of SQL provided by a given system, the SQL standard provides a description of the language that allows for the portability of database applications. Typically, identifiers in the SQL standard are limited to a maximum of 128 charac- ters and the first character must be a letter (upper- or lowercase) and the remaining may be any letter, digit or underscore. Usually, identifiers are case insensitive in SQL, meaning that the iden- tifiers tableName and tablename are considered identical. (This is dependent on the installation options of the database product being used.) Thus, the syntax conventions for identifiers used in this book are not inconsistent with the SQL standard. Specifically, the SQL examples continue to use identifiers starting with a lowercase letter to refer to the name of a relation or attribute. NOTE 5.1 SQL Syntax Although SQL is a standard, each database product is an approximation to that standard. This text intro- duces the SQL standard where queries preceded by a checkmark ( √ ) in this chapter have been verified using the MySQL database. See the book’s companion website for supporting files.

  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)

  In general, most situations where you can use a subquery will treat the value of the subquery as the null value if the cardinality is 0. Similarly, when the cardi- nality is required to be no greater than 1, you'll get an error if the subquery iden- tifies more than one row. 318 Chapter 9 Advanced SQL Query Expressions By contrast, when a scalar subquery is required (degree and cardinality = 1), SQL:1999 simply doesn't permit you to specify a subquery that returns more than a single column: any attempt to do so will give you syntax errors. Similarly, for a row subquery or a table subquery, the number of columns returned (the de- gree of the subquery) must be appropriate for the use of the subquery. As you've seen in other chapters of this book, and in other sections of this chapter, subqueries can appear in various places throughout a query. They can appear in the select list, but that's not really very common. In fact, programmers who t~ to use a subquery this way often fall into traps that lead to incorrect results, as the following que~ (from Chapter 5, Values, Basic Functions, and Expressions) illustrates: SELECT ti tl e, ( SELECT MAX ( current_dvd_sale_pri ce ) FROMmovie titles ) FROMmovie titles As we saw in Chapter 5, that query returns the name of every movie, with each name accompanied by the price of the most expensive mo~e in our database-- probably not what the application really needs. Much more frequently, you'll use subqueries in a WHERE clause, like this (also found in Chapter 5, Values, Basic Functions, and Expressions): SELECT t i tl e, current_dvd_sal e_pri ce FROMmovie titles WHERE current_sal e_pri ce = ( SELECTMAX ( current_dvd_sale_price ) FROMmovie titles ) or in a predicate that is in a WHEREclause, like one taken from Chapter 7, Predicates, replicated in Example 9-13.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Introduction to Database and Knowledge-Base Systems

  • S Krishna(Author)
  • 1992(Publication Date)
  • WSPC

    (Publisher)

  4.1.3 Nested Queries Nesting of queries in SQL was visualized as a feature which would simplify query expression and also improve readability for more complex queries. We may assume, for comprehension, that the system evaluates the 74 Relational Database Languages and Systems inner or nested subquery first and then the outer subquery. The query, how-ever, can be expressed in alternative forms to obtain the same output. Q9: Get date and value of sales made to customer Donald. SELECT SDATE, VALUE FROM SALE WHERE CNO LN (SELECT CNO FROM CUSTOMER WHERE NAME = 'Donald') In the present example, an alternate expression is SELECT SDATE, VALUE FROM SALE, CUSTOMER WHERE SALE.CNO = CUSTOMER.CNO AND CUSTOMER.NAME = 'Donald' Multiple levels of nesting is also possible as in the following: Q10: Obtain details of salesmen who have sold airconditioners. SELECT * FROM SALESMAN WHERE SNO IN (SELECT SNO FROM SALE WHERE PNO LN (SELECT PNO FROM PRODUCT WHERE DESCRIPTION = 'Airconditioner')) This query again may alternatively be expressed in terms of a join of three tables as in Q7. The EXISTS clause represents the existential quantifier of predicate calculus. The negated clause NOT EXISTS is useful in queries which in some form require all or none values. 4.1 SQL 75 In its simple form EXISTS can be used instead of IN for subqueries as in the following rephrasing of query Q10 below. Qll: Obtain details of salesmen who have sold airconditioners. SELECT* FROM SALESMAN WHERE EXISTS (SELECT SNO FROM SALE WHERE PNO IN (SELECT PNO FROM PRODUCT WHERE DESCRIPTION- 'Airconditioner* AND SALE PNO - PRODUCT.PNO AND SALE. SNO = SALESMAN. SNO)) Q12: Get details of customers who have not bought a Washing Machine. SELECT* FROM CUSTOMER WHERE NOT EXISTS (SELECT * FROM SALE WHERE PNO IN (SELECT PNO FROM PRODUCT WHERE CUSTOMER.CNO=SALE.CNO AND SALE.PNO=PRODUCT.PNO AND PRODUCT.DESCRIPTION= 'Washing Machine')) Q13: Get details of customers who have purchased every product.

  Sign up to read

  Learn more about book