Limit in subquery mysql error

Дата: 25.10.2013

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

В статье рассмотрены ошибки, возникающие из-за ограничений сервера MySQL на выполнение некоторых видов подзапросов. Даны рекомендации по их исправлению с примерами. Материал ориентирован на новичков.

MySQL error 1093

В MySQL нельзя изменять данные и одновременно делать выборку из той же таблицы в подзапросе. Запросы вида

DELETE FROM t WHERE col = (SELECT … FROM t …);
UPDATE t … WHERE col = (SELECT … FROM t …);
{INSERT|REPLACE} INTO t (SELECT … FROM t …);

приведут к ошибке

ERROR 1093 (HY000): You can‘t specify target table ‘t‘ for update in FROM clause.

Есть два варианта решения проблемы:

1. Универсальный способ, рекомендуемый в документации, — использовать вложенный подзапрос.

DELETE FROM t WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);
UPDATE t … WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);
{INSERT|REPLACE} INTO t (SELECT * FROM (SELECT … FROM t…) AS t1);

В этом случае подзапрос к изменяемой таблице оказывается в части FROM и материализуется во временную таблицу в начале выполнения запроса. Т.о. при обновлении чтение данных будет идти из временной таблицы, а не из той, которая обновляется.

2. Для запросов UPDATE и DELETE можно использовать многотабличную форму. Например, для UPDATE запрос выше примет вид:

UPDATE t, (SELECT … FROM t …) t1 … WHERE t.col=t1.col;

По сути это тот же метод, что и предыдущий — подзапрос переносится в часть перечисления таблиц. Но кроме чуть более компактной записи многотабличная форма операторов UPDATE/DELETE в некоторых случаях позволяет вообще обойтись без подзапроса.

Примеры:

• мы хотим удалить из первой таблицы строки, не имеющие соответствия (по id) во второй.

  DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

  Для сравнения через подзапрос:

  DELETE FROM t1 WHERE id IN (SELECT * FROM (SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL) as t_x);

• удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id

  DELETE t1 FROM t t1 JOIN t t2
  ON t1.col = t2.col AND t1.id < t2.id;

  через подзапрос

  DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;

• а вот задача пометить важными статьи авторов, имеющих более 10 публикаций, без подзапроса не решается:

  UPDATE articles SET important=1 WHERE author IN (SELECT * FROM (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t);

  или

  UPDATE articles, (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t SET important=1  WHERE author = t.author;

• в завершение рассмотрим пример, когда подзапрос находится в части SET. Например, для строк с id>10 мы хотим установить значение поля col равное значению этого поля для строки с id равным 2.

  UPDATE t as t1 JOIN t as t2 ON t2.id=2 SET t1.col = t2.col WHERE t1.id > 10;

  через подзапрос

  UPDATE t
      SET col = (SELECT * FROM (SELECT col FROM t WHERE id = 2) AS t1)
      WHERE id = >10;

MySQL error 1235

В MySQL не реализована возможность использования IN/ALL/ANY/SOME вместе с подзапросом, содержащим LIMIT. Попытка выполнить такой запрос приведет к ошибке:

mysql> SELECT * FROM t1
    ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn‘t yet support
 ‘LIMIT & IN/ALL/ANY/SOME subquery‘

Данное ограничение обходится путем переписывания запроса через JOIN с нужным подзапросом. Например:

SELECT * FROM t1 WHERE t1.col1 IN (SELECT col2 FROM t2 WHERE x);

можно переписать как

SELECT * FROM t1 JOIN (SELECT DISTINCT col2 FROM t2 WHERE x) t ON t1.col1=t.col2;

Если t2.col2 — уникальный ключ, то DISTINCT не потребуется и от подзапроса можно избавиться:

SELECT * FROM t1 JOIN t2 ON t1.col1=t.col2 WHERE t2.x;

На практике IN подзапросы более распространены и в большинстве случаев указанного правила будет достаточно. Некоторые виды ALL/ANY подзапросов могут потребовать более сложных преобразований, но переписать через JOIN можно любой их них.

Например, мы хотим найти страны в Европе, у которых каждый из трех самых крупных городов имеют население свыше миллиона. Если бы не запрет на использование LIMIT в ALL подзапросах, то решением было:

SELECT name FROM country WHERE continent=‘Europe’ AND
1000000 < ALL (SELECT population FROM city WHERE countrycode=code OREDR BY 1 DESC LIMIT 3);

через JOIN данный запрос примет вид:

SELECT country.name FROM country JOIN city ON countrycode=code
WHERE continent=‘Europe’ AND city.population>1000000
GROUP BY 1 HAVING(count(*)>2);

Замечание: Использовать вложенный подзапрос при mysql error 1235 является плохой идеей:

1. этот прием сработает только в случае независимых подзапросов;

2. на старых версиях (до MariaDB5.3/MySQL5.6) будет иметь худшую производительность, а в новых оптимизатор сам перепишет запрос через JOIN

P.S. Если после прочтения статьи ваш вопрос с MySQL Error 1093 или 1235 остался нерешенным, то задавайте его на форуме SQLinfo

Дата публикации: 25.10.2013

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Answer by Esperanza Weber

This should inject a WHERE IN clause in our base query. But, as we saw earlier this is not going to work due to MySQL’s subquery limitations when using specific clauses like WHERE IN., Limit MySQL subquery results inside a WHERE IN clause, using Laravel’s Eloquent ORM,So, we found a way to override the MySQL limitation. Now, let’s find out how we can implement this in Laravel’s Eloquent ORM.,Let’s assume we have the following models in our Laravel application:

Our database consists of two tables. The first one is the users table, having an id and an email column. The second one is the posts table, having an id, a title, and a user_id column. The user_id column acts as a foreign key for the id column of the users table. Let’s see how our data looks by joining the two tables with a simple query:

SELECT usres.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id;

After reviewing our database structure, it’s time to start playing around. Let’s try to write a query that returns each user along with their latest post. We assume that the post order is determined through the post_id column, so the latest post is the one with the highest id. Therefore, a query like the following should do the job for us:

SELECT users.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id 
WHERE posts.id IN (
    SELECT id FROM posts p2 
    WHERE p2.id = posts.id
    ORDER BY id DESC
    LIMIT 1
);

Unfortunately, executing the above query produces the following error:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

The fastest way to fix this problem is by wrapping the original subquery inside another subquery. By creating an external query with a wildcard selector, we can then perform the original query inside it, as the external query doesn’t use the LIMIT clause. Let’s see what the following query returns when it gets executed:

SELECT users.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id
WHERE posts.id IN (
  SELECT * FROM (
    SELECT id FROM posts sq_posts
    WHERE sq_posts.user_id = posts.user_id
    ORDER BY sq_posts.id DESC
    LIMIT 1
  ) as sq
);

Our User model has a function defining a relationship to the Post model:

function posts() {
  return $this->belongsToMany(AppPost::class);
}

So, if we run the following code we will get a list of users along with their posts:

User::with('posts')
      ->get();

The next step is to make our query to return only the latest post for each user. To do so, we will use a feature that Eloquent provides called query scopes. Query scopes allow us to inject filters in our queries, defined as model functions. In our example, we would do something like the following.

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        $query->select('id')
              ->from('posts sq_posts')
              ->whereRaw('sq_posts.user_id = posts.user_id')
              ->orderBy('sq_posts.id', 'desc')
              ->limit(1);
    });
}

The answer is simple: By using Eloquent’s built-in function called fromSub. Let’s see how the Eloquent query would look like, and then explain it in more detail.

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        return $query->fromSub(function($subQuery) {
            $query->select('id')
                  ->from('posts sq_posts')
                  ->whereRaw('sq_posts.user_id = posts.user_id')
                  ->orderBy('sq_posts.id', 'desc')
                  ->limit(1);
        }, 'sq');
    });
}

The fromSub function exposes the same functions as every other Eloquent query. For example, if we want to limit the external subquery results to match only posts with an id of 3 or higher (normally we would do this in the inner subquery but just to demonstrate how it’s done, we will do it in the outer one for this example), we could write the following:

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        return $query->fromSub(function($subQuery) {
            $query->select('id')
                  ->from('posts sq_posts')
                  ->whereRaw('sq_posts.user_id = posts.user_id')
                  ->orderBy('sq_posts.id', 'desc')
                  ->limit(1);
        }, 'sq')->where('id', '>', 3);
    });
}

Answer by Daleyza Brady

Break out your subquery using php. Grab the ids, then format the results into a comma separated string.,MySQL does support LIMIT in a subquery…but MySQL does NOT support using IN/NOT IN with a subquery.,

Stack Overflow for Teams
Where developers & technologists share private knowledge with coworkers

,Is there a workaround for LIMIT in subquery?

Sorry I’m late, this worked for me:

   SELECT p.id 
     FROM properties p
LEFT JOIN (SELECT t.id
             FROM PROPERTIES t
            WHERE t.vendor = 'abc'
         ORDER BY RAND()
            LIMIT 30) x ON x.id = p.id
    WHERE x.id IS NULL
    LIMIT 10, 20

Answer by Kamden Patterson

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.,Also note that I removed the last group by r.ran_resId from your query.,Thanks for contributing an answer to Database Administrators Stack Exchange!,It makes no sense to group by a and select b, c in a query. And while MySQL allows it, it is a guaranteed way to get garbage results.

If 2 is a fixed value and you’ll never need to compare against 3, 4 or more items in the future, then you could convert the subquery to a derived table and find the 2 values with aggregation:

select r.res_id, r.res_name
from Resource as r
     join
     ( select min(lim.ran_resid) as ran_resid_1,
              max(lim.ran_resid) as ran_resid_2 
       from
         ( select v.ran_resid
           from views as v
           order by v.viewsid
           limit 2
         ) as lim
     ) as x
     on r.ran_resid NOT IN (x.ran_resid_1, x.ran_resid_2)
 ;  

A more general solution (where you can use any LIMIT and not just 2), is to write the antijoin with the LEFT JOIN / IS NULL method:

select r.res_id, r.res_name
from Resource as r
     left join
     ( select v.ran_resid
       from views as v
       order by v.viewsid
       limit 5
     ) as x
     on r.ran_resid = x.ran_resid
where x.ran_resid is null
 ; 

An NOT EXISTS version should work as well but I’m not sure that it would be very efficient, due to the double nesting:

select r.res_id, r.res_name
from Resource as r
where not exists
  ( select 1
    from
      ( select v.ran_resid
        from views as v
        order by v.viewsid
        limit 5
      ) as x
    where r.ran_resid = x.ran_resid
  ) ;

Answer by Tatum Daniel

Row comparison operations are only partially supported:
,
In general, you cannot modify a table and select from the
same table in a subquery. For example, this limitation
applies to statements of the following forms:
,
Here the result from the derived table is materialized as a
temporary table, so the relevant rows in
t have already been selected by the time
the update to t takes place.
,
The reason for supporting row comparisons for
IN but not for the others is that
IN is implemented by rewriting it as a
sequence of =
comparisons and AND operations.
This approach cannot be used for ALL,
ANY, or SOME.

In general, you cannot modify a table and select from the
same table in a subquery. For example, this limitation
applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if for
the modified table you are using a derived table and that
derived table is materialized rather than merged into the
outer query. (See
Section 8.2.2.4, “Optimizing Derived Tables and View References with Merging or
Materialization”.) Example:

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);

Answer by Deborah Cuevas

i want to create Stored PROCEDURE with multi statement, and it not working , and Google the problem and found that mysql dose not support Subquery statement «MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery'»,I have a sneaking (and optimistic) feeling that the issue here is just SELECTing on the same table you perform update on. The ORDER BY is dependent on the exact ID you are changing. I think alias miht help, so give different alias and try:,I don’t have the means to test now =(
Let me know if it works!,Get answers to millions of questions and give back by sharing your knowledge with others.

My statement like this:

DROP PROCEDURE IF EXISTS proc_Name;
CREATE PROCEDURE `DBName`.`proc_Name`()
BEGIN
    SELECT FROM table1 WHERE ORDER BY table1_Colom LIMIT 100;
    UPDATE table2 SET table2_colom1 = 1 WHERE ID IN  (SELECT ID FROM table2 ORDER BY table2_colom1 LIMIT 100);
END ;

Answer by Aryan Robertson

The limit keyword is used to limit the number of rows returned in a query result.,
The LIMIT keyword of is used to limit the number of rows returned from a result set.,
It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax,It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax

The syntax for the LIMIT keyword is as follows

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

Let’s look at an example –

SELECT *  FROM members LIMIT 2;

SELECT *  FROM members LIMIT 2;

membership_ number
full_ names
gender
date_of _birth
date_of _registration
physical_ address
postal_ address
contact_ number
email
credit_ card_ number


1
Janet Jones
Female
21-07-1980
NULL
First Street Plot No 4
Private Bag
0759 253 542
[email protected]
NULL


2
Janet Smith Jones
Female
23-06-1980
NULL
Melrose 123
NULL
NULL
[email protected]
NULL

Let’s suppose that we want to get a list of the first 10 registered members from the Myflix database. We would use the following script to achieve that.

SELECT *  FROM members LIMIT 10;

Re-writing the above script as follows

SELECT *  FROM members LIMIT 9;

Let’s suppose that we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword together with the offset value to achieve that. The script shown below gets data starting the second row and limits the results to 2.

SELECT * FROM `members` LIMIT 1, 2;

Executing the above script in MySQL workbench against the myflixdb gives the following results.

membership_ number






















full_ names






















gender






















date_of _birth






















date_of _registration






















physical_ address






















postal_ address






















contact_ number






















email






















credit_ card_ number
















































2






















Janet Smith Jones






















Female






















23-06-1980






















NULL






















Melrose 123






















NULL






















NULL






















[email protected]






















NULL
















































3






















Robert Phil






















Male






















12-07-1989






















NULL






















3rd Street 34






















NULL






















12345






















[email protected]






















NULL



























Note that here OFFSET = 1 Hence row#2 is returned & Limit = 2, Hence only 2 records are returned

Answer by Kaylee Pope

A «table» (and subquery in the FROM clause too) is — according to the SQL standard — an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That’s why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY … LIMIT … changes the result, the set of rows, not only their order). ,You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.,Query with ORDER BY in a FROM subquery produces unordered result. Is this a bug?
Below is an example of this:,

How many JOIN Clauses are Allowed in a Query?

Query with ORDER BY in a FROM subquery produces unordered result. Is this a bug?
Below is an example of this:

SELECT field1, field2 FROM ( SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

Answer by Lance Harrell

Instead, you can employ a multi-table update in which the subquery
is moved into the list of tables to be updated, using an alias to
reference it in the outermost WHERE clause,
like this:
,
If you set a column to the value it currently has, MySQL notices
this and does not update it.
,
You cannot update a table and select directly from the same table
in a subquery. You can work around this by using a multi-table
update in which one of the tables is derived from the table that
you actually wish to update, and referring to the derived table
using an alias. Suppose you wish to update a table named
items which is defined using the statement
shown here:
,
You can use LIMIT
row_count to restrict the
scope of the UPDATE. A
LIMIT clause is a rows-matched restriction. The
statement stops as soon as it has found
row_count rows that satisfy the
WHERE clause, whether or not they actually were
changed.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

If you access a column from the table to be updated in an
expression, UPDATE uses the current
value of the column. For example, the following statement sets
col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets
col2 to the current (updated)
col1 value, not the original
col1 value. The result is that
col1 and col2 have the same
value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

If an UPDATE statement includes an
ORDER BY clause, the rows are updated in the
order specified by the clause. This can be useful in certain
situations that might otherwise result in an error. Suppose that a
table t contains a column id
that has a unique index. The following statement could fail with a
duplicate-key error, depending on the order in which rows are
updated:

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in the
id column and 1 is updated to 2 before 2 is
updated to 3, an error occurs. To avoid this problem, add an
ORDER BY clause to cause the rows with larger
id values to be updated before those with
smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

You can also perform UPDATE
operations covering multiple tables. However, you cannot use
ORDER BY or LIMIT with a
multiple-table UPDATE. The
table_references clause lists the
tables involved in the join. Its syntax is described in
Section 13.2.9.2, “JOIN Clause”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

You cannot update a table and select directly from the same table
in a subquery. You can work around this by using a multi-table
update in which one of the tables is derived from the table that
you actually wish to update, and referring to the derived table
using an alias. Suppose you wish to update a table named
items which is defined using the statement
shown here:

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

To reduce the retail price of any items for which the markup is
30% or greater and of which you have fewer than one hundred in
stock, you might try to use an UPDATE statement
such as the one following, which uses a subquery in the
WHERE clause. As shown here, this statement
does not work:

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

Instead, you can employ a multi-table update in which the subquery
is moved into the list of tables to be updated, using an alias to
reference it in the outermost WHERE clause,
like this:

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

Because the optimizer tries by default to merge the derived table
discounted into the outermost query block, this
works only if you force materialization of the derived table. You
can do this by setting the
derived_merge flag of the
optimizer_switch system variable
to off before running the update, or by using
the NO_MERGE optimizer hint, as
shown here:

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

Another possibility is to rewrite the subquery so that it does not
use IN or EXISTS, like this:

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.

Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'.

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

error: more than one row returned by a subquery used as an expression

Посмотрим, что там с данными не так?

SELECT e.store_id,
       e.rank_id,
       e.last_name || ' ' || e.first_name AS full_name 
  FROM employee e
 WHERE e.rank_id = 'MANAGER'
 ORDER BY e.store_id, e.last_name, e.first_name

В магазине 201 два менеджера, а в магазине 600 — три.

В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.

Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.

В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:

• должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
• использовать агрегатную функцию;
• использовать LIMIT 1 для ограничения количества строк.

Воспользуемся LIMIT 1:

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
         ORDER BY e.last_name,
                  e.first_name,
                  e.middle_name
         LIMIT 1
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg:

SELECT s.store_id,
       s.name,
       (SELECT string_agg (
                  e.last_name || ' ' || e.first_name, '; '
                  ORDER BY e.last_name,
                           e.first_name
               )
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employees
  FROM store s
 ORDER BY s.store_id

Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.