Correlated Subquery Mysql

for the nested, correlated subquery? Is there some sort of nesting limit? This subquery needs to be ran for every row in the resultset because the results will change based on the content_id, which can be different for each row. MYSQL关联子查询(Correlated Subqueries)_neconano_新浪博客,neconano, EXPLAIN的输出表明,film_actor的select_type是DEPENDENT SUBQUERY,所以MYSQL将会对film表做全表. In correlated subquery the subquery will be executing once and the will substitute the resulting value into WHERE clause of the outer query. Today, I am providing an article showing you how to add a subquery to a select statement in SQL Server 2012. • Professional Linux Programming (ISBN 1-861003-01-3) contains chapters on the PostgreSQL and MySQL database systems. Correlated Subqueries : Correlated Subqueries « Subquery « MySQL Tutorial. It is still best practice to avoid the correlated subquery when possible. Also see Row Subqueries, Subqueries with EXISTS or NOT EXISTS, Correlated Subqueries and Subqueries in the FROM Clause. If the subquery is placed in the WHERE or HAVING clause of an outer query, the subquery can be placed only on the right side of the comparison operator. I'm assuming thats just a typo rather than a misunderstanding. Demonstrates SQL correlated subqueries. While my examples are only tested in MySQL, I should note that the correlated subquery syntax is a part of standard SQL, so you should be able to use it in pretty much any relational database platform. The correlation criteria in the inner query must use the equality operator, "=". support these equalities. This article describes SQL subqueries and their common use cases in Azure Cosmos DB. What is a correlated subquery? a subquery that is processed, or executed once for each row in the outer query. Query #1, involving a simple join, runs fine in both PROC SQL and SQL Server's T-SQL. The subquery cannot contain a group by or connect by reference. This is the main difference between a correlated subquery and just a plain subquery. Stored Functions. Difference between Subquery, Nested Subquery and Correlated Subquery Query. Id) FROM Customer C This is a correlated subquery because the subquery references the enclosing query (i. Query 5-25 is an example of a correlated subquery that returns a list of the 10 latest shipping dates in the orders table. Demonstrate to me an "on purpose not in correlated subquery". A correlated subquery refers to one or more columns from outside of the subquery. Today is the first time I had to look at MySQL performance. Starting from the just released MySQL 5. A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list. I wonder if PG folks at some point in the past have made a conscious decision not to bother optimizing correlated subqueries. It means you can execute the subquery as a single query. Subquery or Inner query or Nested query is a query in a query. SQL subquery basic. SET and Correlated Subquery. If you are used to some other RDBMS than MySQL, then you might not know about the MySQL specific multi. The correlated subquery must use the EXISTS clause. New subquery optimizations in MySQL 6. com Background: subquery processing before 6. January 12, 2019 06:05AM Re: Nested Subquery and Correlated Subquery. In this lesson, we will learn about correlated SQL sub-queries and their uses with some examples. The correlated subquery gets executed repeatedly, once for each row that is selected by the subquery. -- Trigger is valid for multirow and single-row inserts. `from_date` AS `from_date`, `employees`. `title` AS `title`, `employees`. A correlated subquery, however, executes once. Correlated Subqueries. Correlated Subqueries - SQL Server 2012 A subquery is said to be a correlated subquery if the inner query depends on the outer query for any of its values. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value. The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it's WHERE clause. So the inner query will get executed as many times as no. It demonstrates that the subquery uses data from the outer query and the subquery executes once for every row in the outer query. SQL's standard order of execution is not always what is required, as was seen in the lectures covering the SQL GROUP BY facility. Correlated subqueries. I mean, Subquery depends upon main query, and vice versa. Difference between subquery and correlated subquery in Teradata SQL? Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query. Subquery tdak boleh digunakan sebagai operan di ekspresi. The outer query output can use the inner query output for comparison. It means that you can execute the subquery as a normal query. In MySQL, a subquery is a query within a query. Which doesn't mean that you cannot DO this with MySQL. Because of this the correlated subquery will be executed over, and over again. The subquery that we have seen so far can execute independently. Summary: in this tutorial, we will show you how to use the MySQL subquery to write complex queries and explain the correlated subquery concept. Also, the subquery cannot contain a COMPUTE or FOR BROWSE clause, and cannot contain an ORDER BY clause unless the TOP operator is included in the SELECT clause. department AND. Correlated Subquery. Correlated sub-query is a sub-query that executes once for each outer query value (or record). 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. 45)? Thanks in advance. Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query. Correlated subqueries. A SUBQUERY is a query inside a query. The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which can be frustrating. CustomerID (noncorrelated subquery) or. Here is an example of Correlated Subqueries:. Demonstrate to me an "on purpose not in correlated subquery". For each row processed in the parent query, the correlated subquery is evaluated once. A correlated subquery is evaluated once for each row processed by the parent. Whenever Netezza SQL encounters a regular subquery, it precalculates the subquery once as the example in Subqueries. -The process is repeated for all rows. When to write a correlated subquery. New subquery optimizations in MySQL 6. A non-correlated subquery, I think, is where the results of the outer query depend on the inner. The subquery and the outer query are said to be correlated, as they are linked to each other. Unlike the subqueries above, each of these inner queries need to be run for each of the rows in the outer query, since the inner query is dependent on the current. SQL subquery is usually added in the WHERE Clause of the SQL statement. 2) A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Chapter 8 Subqueries Subquery Concepts A SELECT statement nested within the FROM, WHERE, or HAVING clause of another SQL statement The subquery (inner query) executes once , before the main query The result of the subquery is passed to the main query (outer query) 2 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table );. The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. A subquery is a SELECT statement which is used in another SELECT statement. SQL subquery is usually added in the WHERE Clause of the SQL statement. My question is about correlated subqueries, something I was taught about in my Oracle Database SQL Fundamentals course I took a few weeks ago. Join 338 other followers. A common myth in SQL is the idea that correlated subqueries are evil and slow. Subquery tdak boleh digunakan sebagai operan di ekspresi. A few types of correlated subqueries follow patterns that Amazon Redshift can't decorrelate and doesn't support. The correlated subquery must use the EXISTS clause. A reference to variables in an outer expression by a variable in an inner expression is called an outer reference. Three of our previous books (published by Wrox Press) contain information about programming with data: • Beginning Linux Programming, Third Edition (ISBN 0-7645-4497-7) covers the DBM library and the MySQL database system. Correlated Subqueries. A correlated subquery is useful when you need to compare every row of the outer query's results with the result of the inner query. I know my SQL knowledge isn't stunning, but doesn't your suggestion pull the. ticketid' then? My thinking about that would be that, while MySQL supports correlated subqueries, what you're trying to do seems like it could fail in a join because the 'inner' query isn't really 'inside' the rest of the query like it is in a WHERE clause. A subquery is also called an inner query. Using the query below, I want to update *every* record in Table1 with a corresponding value in Table2. Although we've been looking at SQL Server syntax, the concept is the same for other tools. Subquery or Inner query or Nested query is a query in a query. Subquery tdak boleh digunakan sebagai operan di ekspresi. In the previous examples, we see the subquery itself is independent. In this article I will briefly describe a subquery vs a correlated subquery in SQL. I imagine the problem is 'cfv. - [Instructor] We can also use a type of subquery known as…a correlated subquery. For each row processed in the parent query, the correlated subquery is evaluated once. The correlated subquery calculates total sales for each selected customer from the customers table. teamid, points from player t1 where t1. Many people are confused by SQL terminology, particularly by subqueries and derived tables, and use the terms incorrectly. For example, this query here: SELECT first_name, last_name, (SELECT count(*) FROM film_actor fa WHERE fa. optimizations in MySQL 6. orders_department ) = ( SELECT sum(a. Possible future optimization: MySQL does not rewrite the join order for subquery evaluation. In this article. A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause. For instance this query can be easily rewritten to use a join. Previous SQL query was also using subquery but it was non-correlated, this solution will use correlated subquery. These are really important queries which will ask in most of the interview questions. org Summary: in this tutorial, we will show you how to use the MySQL subquery to write complex queries and explain the correlated subquery concept. WHERE EXISTS tests for the existence of any records in a subquery. 36, if you compare a NULL value to a subquery using ALL, ANY, or SOME, and the subquery returns an empty result, the comparison might evaluate to the non-standard result of NULL rather than to TRUE or FALSE. While processing Correlated subquery: The first row of the outer query is fetched. A blog for SQL Server best practices, performance considerations, advanced TSQL techniques and handy tips and tricks. Performing Multiple-Table Retrievals with Subqueries. This type of subquery is called a simple subquery. SQL Subqueries. Row sub queries only return a single row but can have more than one column. 45)? Thanks in advance. A correlated SQL subquery is just a subquery that is executed many times—once for each record (row) returned by the outer (main) query. A correlated subquery is the subquery which value depends on the outer query. These kind of Subqueries are interdependent. The outer query tests whether. In some situations, you must use a correlated subquery to obtain the correct reply or result data set. A query can benefit from rewriting a grouping LEFT JOIN as a correlated subquery. A Correlated subquery however executes once for each candidate row considered by the outer query. The inner query is not dependent on outer query. Keep in mind, however, that using correlated subqueries might be inevitable in some cases. WORKDEPT in the WHERE clause of the subselect. Nested and correlated subqueries show up in the WHERE clause of a SQL statement. SQL subquery basic. Correlated subqueries are used for row-by-row processing. non-correlated subquery is a sub query that is an independent of the outer query and it can executed on it's own without relying on main outer query. kudvenkat 145,498 views. We are talking about a correlated subquery, not a subquery. Sergey Petrunia [email protected] I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. CustomerID. In other words, a correlated subquery depends on the outer query. "Hi, I have a correlated subquery which is running long time in oracle. Correlated sub queries as you all know are used for row-by-row processing. column1 (correlated subquery) are equal to ANY of the values retrieved by the SubQuery. This topic is the correlated subquery. For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list. Note that not all SQL DBMSs can be relied upon to meet the SQL3 standard, but all should cope with subqueries in the WHERE phrase, which is the traditional location for subqueries and the most useful. In that case, we can speed things up by rewriting the query to use a single subquery, only scanning the widgets table once: select * from users join (. In correlated subquery the subquery will be executing once and the will substitute the resulting value into WHERE clause of the outer query. SubQuery Example in SQL – Correlated vs Noncorrelated SubQuery in SQL is a query inside another query. 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. New SQL developers often flee to the comfort of procedural programs when it comes to update statements. Correlated subqueries provide an intuitive syntax for writing queries that return related data. or create a view with just this subquery create view yourview as select t1. There’s a better way than creating endless temporary tables, though. Subqueries are nested, when the subquery is executed first,and its results are inserted into Where clause of the main query. Types of SQL Server Sub Queries: Self-Contained, Correlated, Scalar, Multi-Valued, Table-Valued A Sub query is a SELECT statement that is embedded to another query. Unlike the subqueries above, each of these inner queries need to be run for each of the rows in the outer query, since the inner query is dependent on the current. id = ( select id from widgets where widgets. SQL's standard order of execution is not always what is required, as was seen in the lectures covering the SQL GROUP BY facility. Logically, SQL Server evaluates the subquery, replaces it with the list of values it returns, and then evaluates the [NOT] IN. CustomerID (noncorrelated subquery) or. If there is no corresponding record in Table2 I want Table1. Today is the first time I had to look at MySQL performance. Also see Row Subqueries, Subqueries with EXISTS or NOT EXISTS, Correlated Subqueries and Subqueries in the FROM Clause. Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query. However, if the subquery can return a NULL, then NOT IN returns no rows at all. Correlated subqueries sometimes let to create the query very briefly, which can look more cumbersome when you use other means. Types of SQL Server Sub Queries: Self-Contained, Correlated, Scalar, Multi-Valued, Table-Valued A Sub query is a SELECT statement that is embedded to another query. A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result. If SQL is a foreign language, you can mock up a query like the subquery you need, switch it to SQL View, copy, and paste into SQL View in your main query. Problem with subquery and LOCK TABLES - MySQL. The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it's WHERE clause. Beware of nulls in the subquery of anti-joins though: whenever one or more rows return a. SELECT CompanyName FROM Supplier WHERE EXISTS (SELECT ProductName FROM Product WHERE SupplierId = Supplier. Questions: I have a generic question that I will try to explain using an example. The subquery is known as a correlated because the subquery is related to the outer query. The goal of /r/SQL is to provide a place for interesting and informative SQL content and discussions. For example: val IN (SELECT key_val FROM tbl_name WHERE correlated_condition) Otherwise, they are inefficient and likely to be slow. Documentation does talk about correlated subquery in the where clause. The selected customerid from the outer query is passed to the correlated subquery for getting the corresponding sales data. A SQL Server T-SQL correlated subquery is a special kind of temporary data store in which the result set for an inner query depends on the current row of its outer query. The following statement is an example of a correlated subquery. A subquery is a SELECT statement written within parentheses and nested inside another statement. -The results of the correlated subquery are used to determine whether the candidate row should be part of the result set. Row sub queries only return a single row but can have more than one column. New subquery optimizations in MySQL 6. MySQL Subquery - mysqltutorial. SQL subquery basic. correlated subquery: A correlated subquery is a SQL query that depends on values executed by an outer query in order to complete. I thought I would share some of my findings about this little-known feature. Here's an example that looks up the IDs for grade event rows that correspond to tests ('T') and uses them to select scores for those tests:. Correlated subqueries don't work in the same way because they depend on the outer query in order to be processed. January 12, 2019 06:05AM Re: Nested Subquery and Correlated Subquery. While my examples are only tested in MySQL, I should note that the correlated subquery syntax is a part of standard SQL, so you should be able to use it in pretty much any relational database platform. Some queries, however, cannot be completely evaluated before the outer, or main, query is evaluated. Correlated Subquery SQL Server 2014. correlated subquery: A correlated subquery is a SQL query that depends on values executed by an outer query in order to complete. author_id) GROUP BY a. A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query. Difference between subquery and correlated subquery in Teradata SQL? Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query. PostgreSQL. English [Auto] A case let's take a look non correlated queries in the WHERE clause. Performance implications of correlated sub-queries. In most cases, the Netezza correlated subqueries are used to improve the SQL query performance. When using an IN combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole, matching up based on the relationship specified for the IN. Id) FROM Customer C This is a correlated subquery because the subquery references the enclosing query (i. Customer_ID is used in the WHERE clause of the subquery, but OrdLast is an alias for Orders defined in the main query. Start studying MySQL Chapter 7. Hibernate query limitations and correlated sub queries Posted on August 1, 2014 by hedleyproctor It’s well known that one of the key limitations of writing queries in Hibernate, using either HQL or criteria, is that you cannot write queries that have a join in the from clause. NOT IN correlated subquery doesn't sound right. Correlated subqueries are subqueries that depend on the outer query. Subqueries can also be used in INSERT, UPDATE and DELETE queries. A subquery is a query within a query. When to write a correlated subquery. -- SQL GROUP BY Statement Quick Syntax - SQL group by count - group by clause. 2) A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Peter Brawley. Correlated Subquery. That is, the subquery is performed for each row in the parent statement. This article describes SQL subqueries and their common use cases in Azure Cosmos DB. MySQL Subquery - MySQL Tutorial. In the example, the single correlated reference is the occurrence of X. I need to find a solution without using correlated subquery. 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. This is the definition of a correlated subquery - a subquery which depends on rows in an outer query. It first reads a row in the main query, and then goes into the subquery to find all the rows that match the specified column value. If there is no corresponding record in Table2 I want Table1. user_id = users. Correlated subqueries don't work in the same way because they depend on the outer query in order to be processed. Tiny database as web application back-end was having significant performance issues with spikes of CPU workload. If a left-semi join is used then the optimizer rewrote the query. I feel the distinction wasn't quite driven home, though (I missed it on first pass). The subquery is evaluated once for each row processed by the outer query. A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. These are really important queries which will ask in most of the interview questions. weight = (SELECT AVG (weight) FROM person sq WHERE sq. 0 FROM subqueries are pre-materialized (early) Scalar-context subqueries use straightforward evaluation Predicate subqueries May perform two kinds of rewrites Then use straightforward evaluation. Rather than incur the overhead of this correlated subquery, you can write it as a. New SQL developers often flee to the comfort of procedural programs when it comes to update statements. Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query. Correlated EXISTS subqueries are currently supported only in a WHERE clause. - [Instructor] We can also use a type of subquery known as…a correlated subquery. When a subquery has a reference to a table in the outer query, it is called correlated subquery. In that case, we can speed things up by rewriting the query to use a single subquery, only scanning the widgets table once: select * from users join (. Problem with subquery and LOCK TABLES - MySQL. What Is a Subquery? A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. In normal subqueries the. Sql SubQueries. 28, and the resulting query is select `employees`. Creating a Correlated Subquery. Hive does support some of subqueris such as table subquery, WHERE clause subquery etc, and correlated subqueries. Typical example is: SELECT * FROM t1. A query can benefit from rewriting a grouping LEFT JOIN as a correlated subquery. Common SQL keywords that you can use with a subquery. A few types of correlated subqueries follow patterns that Amazon Redshift can't decorrelate and doesn't support. DB2 10 - Application programming and SQL - Correlated subqueries. A SUBQUERY can be further classified into an INLINE VIEW, correlated SUBQUERY or. Use a subquery as a criterion for a query field. Say I have a table with the fields: “id”, “name”, “category”, “appearances” and “ratio” The idea is that I have several items, each related to a single category and “appears” several times. Correlated Subquery. If there is no corresponding record in Table2 I want Table1. CustomerID (noncorrelated subquery) or. Ask Question Asked 5 years, 6 months ago. SELECT CompanyName FROM Supplier WHERE EXISTS (SELECT ProductName FROM Product WHERE SupplierId = Supplier. A correlated subquery differs from a self-contained one in that its value depends upon a variable from the outer query. Since this subquery is correlated, i. This is the main difference between a correlated subquery and just a plain subquery. A subquery that references one or more columns from its containing SQL statement is called a correlated subquery. In this lesson, we will learn about correlated SQL sub-queries and their uses with some examples. A subquery that contains an outer reference is called a correlated subquery. Note that not all SQL DBMSs can be relied upon to meet the SQL3 standard, but all should cope with subqueries in the WHERE phrase, which is the traditional location for subqueries and the most useful. Course Outline. A subquery is a SELECT statement within another statement. A correlated subquery has a join between the subquery and the query that contains it. A plain subquery is not. The response by Toby Thain already answered the question, that a correlated subquery contains a reference to a table in an outer query. The following SQL example uses a simple subquery to obtain a list Using a Correlated Subquery. Summary: in this tutorial, you will learn about the SQL Server subquery and how to use the subquery for querying data. A correlated subquery, however, executes once. T-SQL Programming Part 15 - Understanding How to Write a Correlated Subquery By Greg Larsen There are times when you want to return a set of records that are based on the results of another set of records. Id) FROM Customer C This is a correlated subquery because the subquery references the enclosing query (i. The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. How to UPDATE with a GROUP BY? Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate how to perform a table UPDATE with the results of a GROUP BY query. The approach of the correlated subquery is bit different than normal subqueries. outer query. Like @Andomar pointed out, a function or subquery to the right of a LATERAL join typically has to be evaluated many times - once for each row left of the LATERAL join - just like a correlated subquery, while a plain subquery is evaluated once only. The outer query executes once, using the value from the inner query. A query can benefit from rewriting a grouping LEFT JOIN as a correlated subquery. 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. However, they often perform poorly due to needing to execute once for every value they join on. lastname = p. Unlike noncorrelated subqueries, which are executed exactly once prior to execution of the containing statement, a correlated subquery is executed once for each candidate row in the intermediate result set of the containing query. How to Read Syntax Diagrams. Correlated Subquery. You can also use the correlated subquery in a WHERE clause. The Advantage SQL Engine is optimized to handle this type of correlated subquery and you may not have performance issues. A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. This article describes SQL subqueries and their common use cases in Azure Cosmos DB. We are talking about a correlated subquery, not a subquery. WITH Clause: The WITH query_name clause lets you assign a name to a subquery block. In terms of scope, the inner query can "see" an outer table's columns, but the inner. 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. The SUM function is included in a correlated subquery (the SELECT statement in parentheses). Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the outer query. This stairway level will expand on the subquery topic by discussing a type of subquery known as a correlated subquery, and explores what a correlated subquery is and how it is different from a. …With a correlated subquery, the inner query is. However, they often perform poorly due to needing to execute once for every value they join on. - [Instructor] We can also use a type of subquery known as…a correlated subquery. MySQL subquery is a SELECT query that is embedded in the main SELECT statement. A subquery must be enclosed within a set of parentheses to separate it from the outer query. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. All the above-specified queries are non-correlated subqueries because they run independently. In other words, the outer query returns a table with multiple rows; the inner query then runs once for each of those rows. Practice #1: Using EXISTS in correlated subquery. Query can be defined as a way to inquire the data from the database. report_date = sales. -The results of the correlated subquery are used to determine whether the candidate row should be part of the result set. As with other queries, it is possible to build correlated subqueries in the HAVING clause. A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause. differentiation between those: Correlated subquery is an inner query referenced by main query (outer query) such that inner query considered as being excuted repeatedly. Many SQL questions on Devshed forums are easily solved using correlated and uncorrelated subqueries, but many don't know of them at all, or at least are not shure about the syntax, usage and performance implications, they sometimes rave about a stored procedure to loop and select other values. Uncorrelated EXISTS subqueries are supported anywhere that a boolean expression is allowed. This MySQL tutorial explains how to use subqueries in MySQL with syntax and examples. Table aliases are often required to clarify the relationship between tables in the inner and outer query - especially if they involve the same table. In terms of scope, the inner query can "see" an outer table's columns, but the inner. I feel the distinction wasn't quite driven home, though (I missed it on first pass). A correlated subquery is a subquery that uses the values of the outer query. Consider the following employees and departments tables from the sample database: Suppose you have to find all employees who locate in the location with the id 1700. Correlated subqueries. The main difference between a correlated subquery and a simple subquery is that correlated subqueries reference columns from the outer table. A correlated subquery differs from a self-contained one in that its value depends upon a variable from the outer query. A subquery that contains an outer reference is called a correlated subquery.