MySQL / MariaDB 서브 쿼리 내의 order by 안 되는 현상 + 해결법

작업을 하다 보면 다루어야 할 데이터도 많아지고, 테이블도 많아집니다.

그에 따라서 사용하는 쿼리문이 길어지기도 합니다. 

 

쿼리가 길어지다 보면 과거 심플하던 시절에는 없던 문제가 생기기도 하는데, 이번에 제가 발견한 케이스가 있어 공유하고자 합니다.

사용했던 쿼리의 형태는 아래와 같았습니다. 

 

3개의 테이블을 A 테이블의 m_id를 중심으로 join하고, 그 결과값을 원하는 기준으로 정렬한 후에 특정 컬럼을 기준으로 group하는 쿼리입니다. 

 

SELECT * FROM 

(SELECT A.m_id, A.name, B.gender, C.items FROM A 

JOIN B ON A.m_id = B.m_id 

JOIN C ON A.m_id = C.m_id 

ORDER BY A.regdt DESC ) AS RESULT 

GROUP BY RESULT.regdt 

 

아시다시피 group by는 테이블 내의 row들을 중복제거해주는 효과가 있는데, 공통되는 row가 있을 경우 그 테이블에서 가장 위의 row만 남기는 특성이 있습니다.

group by 결과

이런 느낌이죠. 

그래서 예를 들어 group by 를 할 때 맨 위에 위치한 데이터가 아니라 가장 최신의 row를 남기고 싶다면, group by 이전에 데이터의 정렬이 필요합니다. 

그래서 관련해서 검색을 해보면 위와 같은 쿼리를 쓰라는 자료들이 많습니다.

 

근데 아무리 해도 group by의 결과가 원하는 대로 정렬되어 나오지 않고 항상 똑같이 나와서 뭐지 해서 찾아보니, 아래와 같은 문서가 있었습니다. 

 

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

Why is ORDER BY in a FROM Subquery Ignored?

요약하면 서브쿼리 내의 order by를 설정해도 효과가 없는 것이 맞다는 의미였는데요. 

그 이유는 SQL standard에서 정한 DB 테이블의 정의에서는, 테이블을 채우고 있는 데이터의 order는 아무런 의미가 없기 때문이라고 합니다.

우리가 작성한 서브쿼리 역시 어떻게 보면 한 종류의 테이블이라고 볼 수 있기 때문에(테이블이니까 최상위 절에서 SELECT가 다시 한번 가능한 것이죠), 위와 같이 서브쿼리 내의 order by 명령어가 제대로 동작하지 않았던 것입니다. 

 

해결법은 2가지입니다.

1. 서브쿼리 내의 order by 뒤에 limit를 걸어준다. 

==> 의외로 간단하고 약간은 어이없는 해결책입니다. 서브쿼리 내에 limit를 걸어줄 경우 order by가 먹히는 이유는, limit를 걸게 되면 데이터의 순서뿐만 아니라 갯수까지 제한되에 테이블의 본질적인 내용이 변화된다고 보기에 mysql, mariaDB에는 order by가 적용된다고 합니다. 그러니까...

 

SELECT * FROM 

(SELECT A.m_id, A.name, B.gender, C.items FROM A 

JOIN B ON A.m_id = B.m_id 

JOIN C ON A.m_id = C.m_id 

ORDER BY A.regdt DESC LIMIT 1000000 ) AS RESULT 

GROUP BY RESULT.regdt 

 

이렇게 하면 서브 쿼리 내의 order by가 적용된다는 것이죠. 저도 limit를 붙임으로써 원하는 쿼리 결과물을 얻을 수 있었습니다.

 

2. 문서에서 권하는 두 번째 방식은 order by를 최상위 절로 옮기라는 것입니다. 평소에 쓰는 것처럼 쓰면 됩니다. 다만 저같은 경우에는 group by를 반드시 사용해야 했는데, 최상위 절에서 group by 앞에 order by를 쓸 경우 SQL error가 발생하고, 뒤에 order by를 쓸 경우에는 사용한다 해도 원하는 쿼리 결과물이 아니라서 어쩔 수 없이 배제되었습니다. 

 

오랜만에 글을 쓰려니까 참 어렵네요. 머리가 잘 돌아가지 않는 것도 같고...

그래도 올해 목표 중 하나가 일주일에 1포스팅이라, 앞으로도 열심히 써 보겠습니다. 

 

-끝-

 

https://chisu.tistory.com/4

MySQL / MariaDB 서브 쿼리 내의 order by 안 되는 현상 + 해결법