The EXISTS operator precedes a subquery, which is the only operand. When the outer query and subquery work with different tables, it is typically possible to write the query as uncorrelated. In effect, we are running the subquery over and over again, once for each row we encounter in the outer query.Īs in this example, correlated subqueries tend to be most useful when both the outer query and the subquery work with the same table. While not necessary, we have chosen to alias the subquery’s table ( a2) as well, to avoid any chance of confusion.Īs you can see, we can no longer run the subquery independent of the outer query. To refer to the outer query’s authors table, we must give it an alias ( a1) to distinguish it. If we simply refer to author_id in the subquery, SQL assumes we mean the subquery’s authors table. Note that we have a situation where ambiguity must be resolved using aliasing - we have two instances of the authors table, one used in the outer query and one in the subquery. JOIN books AS b ON a1.author_id = b.author_id Let us return to an example from Chapter 1.4 using our books table: how can we find all books published in the same year as The Three-Body Problem? We will use a subquery (the SELECT query inside the parentheses below) to first obtain the publication_year of The Three-Body Problem, and then we will use that result to get our list of books:Ī1.name AS author, a1.death, b.title, b.publication_year We can use a subquery in place of a scalar expression as long as we know the subquery will return a single row and column. These are appropriate for use within the WHERE clause of another query or statement. To start with, we will examine Boolean expressions using subqueries. The result from a SELECT query is a table in this sense. Here we are using “tables” to mean a collection of rows, not necessarily the named object living in the database. We will see more useful applications of row expressions in the following sections.īeyond rows, we can also think in terms of tables as values. JOIN authors AS a ON a.author_id = b.author_id We can write down a literal row (SQL calls these “row value constructors”) by putting a comma-separated list of expressions between parentheses: Scalar values are simple values of a single type, such as 42 or 'hello'. So far we have assumed that all expressions evaluate to a scalar value. Scalars, rows, and tables ¶īefore we discuss the uses of subqueries, it is useful to talk about some additional types of data that come up in SQL. Tables used in this chapter ¶įor this chapter we will be using the books dataset (tables books, authors, etc.), described in Appendix A. In this chapter we discuss the many ways in which we can use the result of a query within another query. This subquery expression evaluates to a scalar, a row, a column, or a table, depending on the query results and the context in which the subquery appears. A subquery is simply a SELECT query enclosed with parentheses and nested within another query or statement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |