Feb 2, 2023 by Robert Gravelle

Subqueries can be categorized into two types:

• A non-correlated (simple) subquery obtains its results independently of its containing (outer) statement.
• A correlated subquery references values from its outer query in order to execute.

When a non-correlated subquery executes (independently of the outer query), the subquery executes first, and then passes its results to the outer query. Meanwhile, a correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.

Now that we know the difference between a correlated subquery and its non-correlated counterpart, this blog will cover how to write a correlated subquery in Navicat Premium 16.

Syntax and Usage

A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. Here's the syntax for a SELECT query:

SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
  (SELECT column1, column2
   FROM table2
   WHERE expr1 = outer.expr2);

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you can use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.

A Practical Example

Here's a rather ingenious query from stackoverflow against the Sakila sample database that fetches the most viewed film per country.

The first step is to count how many times each film was viewed in each country. Here is the SELECT statement for that:

SELECT 
  F.title AS title, 
  CO.country_id AS country_id,
  CO.country AS country_name, 
  count(F.film_id) as times
FROM customer C INNER JOIN address A ON C.address_id = A.address_id
INNER JOIN city CI ON A.city_id = CI.city_id
INNER JOIN country CO ON CI.country_id = CO.country_id
INNER JOIN rental R ON C.customer_id = R.customer_id
INNER JOIN inventory I ON R.inventory_id = I.inventory_id
INNER JOIN film F ON I.film_id = F.film_id
GROUP BY F.film_id, CO.country_id;

And here is the above query and results in Navicat Premium 16:

The next step is to convert the above results into a list of countries, along with the most viewed film title and the number of times it was viewed. Here's the full query with correlated subquery with an explanation to follow:

Explanation:

• Subquery: Fetches a list of movie count, grouped by country.
• GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||') returns ALL titles in that 'row', with the most-viewed title first. The separator doesn't matter, as long as never occurs in a title.
• SUBSTRING_INDEX('...', '|||', 1) extracts the first part of the string until it finds "|||", in this case the first (and thus most-viewed) title.

Final Thoughts on Correlated Subqueries

In today's blog we learned how to write a correlated subquery using Navicat Premium 16. Be forewarned that correlated subqueries can be slow. However, with proper optimizing, their speed can be increased significantly.