LATERAL JOIN

쿼리 작업을 하던 중, 동일한 값에서 랜덤으로 추출되게 해야하는 일이 있었다.

 

A테이블은 B테이블의 id값을 참조하고 있고,
B테이블에서 해당 id의 code값이 동일한 여러 데이터 중 하나를 조회할 때마다 랜덤으로 보여줘야 했다.

 

즉, B테이블에서 조회한 데이터의 code가 AA_0 이라면

AA_0 이라는 code값을 가지는 여러 데이터 중 하나를 랜덤으로 뽑아서 A테이블과 함께 출력해야 했다.

 

대충 이런 관계

 

 

일반적인 LEFT JOIN을 사용하면 B테이블의 모든 데이터가 조인되버리기에 "하나", "랜덤" 이라는 사항에 맞지 않았다.

 

---

LATERAL JOIN

왼쪽 테이블(A)의 각 행을 순회하면서,  해당 행을 변수로 오른쪽 테이블(B)의 서브쿼리에 전달을 할 수가 있다.

일반적으로 서브쿼리는 외부 값을 참조할 수 없지만, LATERAL JOIN을 이용하면 외부 값을 사용할 수 있다는 것이다.

 

일반적인 JOIN은 준비된 두 집합을 합치는 것이라면,

LATERAL JOIN은 왼쪽 행을 보고, 그 행에 맞춰 오른쪽 집합을 실시간으로 생성하는 것이다.

 

많이 사용되는 예제는 아래와 같다.

 

1. 최신 게시글 3개 뽑기

SELECT
  c.name,
  latest_posts.title
FROM categories AS c
LEFT JOIN LATERAL (
    SELECT 
      p.title
    FROM posts AS p
    WHERE p.category_id = c.id
    ORDER BY p.created_at DESC
    LIMIT 3
) AS latest_posts ON TRUE;

 

2. JSON 데이터 파싱

만약 데이터가 아래와 같이 어떤 컬럼이 JSON형식으로 되어 있다면,

 

name  |  tags (JSON)

홍길동 | ["A", "C"] 

장보고 | ["B"] |

 

홍길동 - A
홍길동 - C
장보고 - B

이런식으로 표현을 하고 싶을때 사용한다.

SELECT 
    m.name, 
    tag_table.tag
FROM members AS m
CROSS JOIN LATERAL JSON_TABLE(
    m.tags,         
    '$[*]' COLUMNS (
        tag VARCHAR(20) PATH '$'
    )
) AS tag_table;

 

JSON_TABLE은 JSON 데이터를 테이블화(표)시켜주는 것이다.

 

1) 행의 tags 값을 JSON_TABLE에 변수로 전달한다.

2) 변수를 순회하면서 name을 가지고 행을 생성한다.

3) 그 다음 데이터로 넘어가면서 동일하게 수행

---

 

 

LATERAL JOIN은 모든 DBMS에서 사용할 수는 없다.

postgresSQL, MySQL(8.0이상), Oracle(12c이상) 등 지원하므로 자신의 DBMS와 버전이 뭔지 확인하고 사용해야한다.