[ON vs WHERE]

ON : JOIN 을 하기 전 필터링을 한다 (=ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)

WHERE : JOIN 을 한 후 필터링을 한다 (=JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)

 

[INNER JOIN 에서의 ON vs WHERE]

1. INNER JOIN + ON 조건절 + ON 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
AND a.key2 = b.key2
 cs

2. EQUI JOIN + WHERE 조건절

1
2
3
4
SELECT *
FROM a AS a , b AS b
WHERE a.key = b.key
AND a.key2 = b.key2
 cs

3. INNER JOIN + ON 조건절 + WHERE 조건절

1
2
3
4
5
SELECT *
FROM a
INNER JOIN b
ON a.key = b.key
WHERE a.key2 = b.key2
 cs

위 세 쿼리는 모두 같은 결과, 같은 퍼포먼스가 나온다.

즉, inner join 에서의 on 조건과 where 조건은 같다.

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

 

[그래도 EQUI JOIN 보단 INNER JOIN 을 써야 하는 이유]

1. 가독성

2. OUTER JOIN 으로 수정이 용이

 

[OUTER JOIN 에서의 ON vs WHERE]

1. OUTER JOIN 정리 (출처)

 

2. OUTER JOIN 에서의 ON vs WHERE

1) OUTER JOIN test 를 위한 샘플 준비.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 샘플 테이블 생성
create table table1 (
  col1 INT,
  col2 VARCHAR(10)
);
create table table2 (
  col1 INT,
  col2 VARCHAR(10)
);
commit;
 
-- 샘플 데이터 입력
insert into table1 (col1, col2) values (1, '하나');
insert into table1 (col1, col2) values (2, '둘');
insert into table1 (col1, col2) values (3, '셋');
 
insert into table2 (col1, col2) values (1, '일');
insert into table2 (col1, col2) values (2, '이');
commit;
 cs

2) 데이터 확인

table1 :

col1 col2
1 하나
2 둘
3 셋

table2 :

col1 col2
1 일
2 이

 

3) OUTER JOIN 에서의 ON

1
2
3
4
5
SELECT t1.col1, t1.col2, t2.col1, t2.col2
FROM   table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
AND t2.col2 = '일';
 cs

위 쿼리의 결과 :

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1 일
2 둘 null null
3 셋 null null

>> table1의 전체 row와 

     table2에서 col2 칼럼 값이 '일'인 로우만 뽑은 row 들을 OUTER JOIN.

>> on 조건은 join 을 하기 전에 필터링 되는 기준

 

4) OUTER JOIN 에서의 WHERE (위 쿼리의 ON 조건을 WHERE 에 그대로 추가)

1
2
3
4
5
select t1.col1, t1.col2, t2.col1, t2.col2
from  table1 t1
LEFT OUTER JOIN table2 t2
ON t1.col1 = t2.col1
where t2.col2 = '일';
 cs

위 쿼리의 결과

t1.col1 t1.col2 t2.col1 t2.col2
1 하나 1 일

>> table1의 전체 row와

     table2 전체 row 를 OUTER JOIN 한 후,

     col2 칼럼 값이 '일'인 로우만 뽑는다.

>> WHERE 조건은 JOIN을 한 이후에 필터링하는 기준

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

[Mysql] 우분투 설치, 유저 생성 및 권한 부여  (4) 2020.01.18
[MySQL] 인덱스(INDEX) 관련 정리  (0) 2020.01.11
SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21

SYSDATE() : SYSDATE() 함수가 호출된 시각

NOW() : NOW() 가 포함된 쿼리(구문(statement))가 호출된 시각

 

[Ex]

1
2
3
4
5
6
7
SELECT
        SYSDATE(), 
        SLEEP(3) AS 'sleep', 
        SYSDATE(),
        NOW(), 
        SLEEP(3) AS 'sleep', 
        NOW();
 cs

 

[결과]

 

[쿼리 한 줄씩 해석]

SELECT

SYSDATE() 함수 호출될 때의 시각 (6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:31)

SYSDATE() 함수 호출될 때의 시각 (6:52:31)

NOW()       쿼리(구문)가 실행될 때의 시각(6:52:28)

SLEEP(3)    3초간 Thread sleep (6:52:34)

NOW()      쿼리(구문)가 실행될 때의 시각(6:52:28)

 

※ SYSDATE와 NOW 는 각각 언제 사용해야 하는가?

- 실행결과에 따른 시각이 중요, 이를 저장해야하는 경우 NOW 사용

- 호출된 시각이 중요한 경우 SYSDATE 사용

ex) Spring Batch/Spring Quartz의 경우 Schedule, Job의 실행시각들이 메타테이블에 의해 관리가 되는데(Schedule, Job 관리가 inMemory 방식이 아닌 jdbc 방식인 경우), 쿼리가 끝난 시각이 아닌 호출시점의 시각이 중요하므로 SYSDATE를 사용하여 관리되고 있다.

 

참고 : https://database.guide/sysdate-vs-now-in-mysql-whats-the-difference/

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

[MySQL] 인덱스(INDEX) 관련 정리  (0) 2020.01.11
JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN  (4) 2019.11.14
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12

Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)

 

고객들이 열람할 가이드 정보가 있고,

가이드 정보의 카테고리를 N depth 로 분류(멀티 셀렉트박스 사용)하여 조회할 수 있게 하여 다음과 같은 화면을 만들어 보자.

<1: 테이블 설계>

1. '전체' 옵션의 유무

case 1 멀티 셀렉트박스에 '전체'라는 옵션이 없는 경우 :

마지막 셀렉트박스의 value(node_id)를 기준으로 가이드를 조회

case 2 멀티 셀렉트박스에 '전체'라는 옵션이 존재하는 경우 :

'전체'가 선택된 마지막 셀렉트박스 직전의 value(node_id)를 기준으로 모든 자손 node_id를 찾아 조회해야한다.

 

예를 들어,

case 1 ex) 고객지원 > 고객지원sub > 고객지원sub의sub 와 같이 마지막 select box에 특정 값이 선택된 경우 :

고객지원sub의sub 의 value(node_id) 를 기준으로 가이드 조회

case 2 ex) 고객지원 > 고객지원sub > 전체 와 같이 마지막 select box가 '전체'로 선택된 경우 :

고객지원sub을 부모 노드로 삼는 모든 자손 노드를 기준으로 가이드 조회

 

2. 동적 depth 여부

case 1 : depth 가 특정 값으로 고정인 경우, 셀렉트박스용 분류 코드테이블을 따로 두고 join 을 몇번 걸면 해결이 된다

case 2 : depth 가 동적(N)인 경우, join 이 N 번 되어야 하므로 단순한 join으로 해결 할 수 없다..

 

전체 옵션이 존재하고 depth 가 동적(N)인 경우를 전재하고 설계 및 개발해보자.

 

 

<2: 테이블 생성>

1. 메인테이블 생성 : 가이드 정보 테이블 생성

가이드 정보 테이블은 제목, 가이드의 노드 아이디, 상세 내용만 지니고 있다.

1
2
3
4
5
6
7
8
CREATE TABLE `guide_info` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  `TITLE` varchar(100) NOT NULL COMMENT '제목',
  `GUIDE_ID` int(11) NOT NULL COMMENT '가이드 노드 아이디',
  `TEXT_ITEM_1` text COMMENT '가이드 내용',
  PRIMARY KEY (`SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='고객지원 가이드 게시판';
 
 cs

 

2. 서브테이블 생성 : 가이드 분류 정보를 따로 지니고 있는 노드 정보 테이블 생성

1
2
3
4
5
6
7
8
9
CREATE TABLE `node_info` ( 
  `NODE_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '노드아이디', 
  `PNODE_ID` int(11) NOT NULL COMMENT '부모노드아이디', 
  `NODE_PATH` varchar(200) DEFAULT NULL COMMENT '노드경로', 
  `NODE_NM` varchar(20) DEFAULT NULL COMMENT '노드명', 
  `DEPTH` int(11) DEFAULT '1' COMMENT '노드DEPTH', 
  `LEAF_YN` varchar(1) DEFAULT 'N' COMMENT '자식노드존재유무', 
  PRIMARY KEY (`NODE_ID`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='노드 정보'; 
 cs

 

생성된 테이블 :

NODE_ID(노드아이디)와 PNODE_ID(부모 노드 아이디)가 서로 참조하는 관계의 테이블 구조.

 

샘플 데이터 :

 

<3: 쿼리 작성>

필요한 쿼리 종류는 크게 두가지로 나눠볼 수 있다.

1. 셀렉트박스의 구성요소인 노드정보(node_info TABLE)를 조회하는 쿼리 : 멀티셀렉트박스를 그리기 위한 데이터를 뽑는 쿼리

2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록(guide_info TABLE)을 조회한 결과 : 조회 결과를 출력하기 위한 데이터를 뽑는 쿼리

 

3-1. 셀렉트 박스의 구성요소인 노드정보를 조회하는 쿼리 :

N번째(Ndepth) 셀렉트박스가 특정값으로 선택된 경우 N+1번째(N+1depth) 셀렉트박스 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  node_id
      , pnode_id
      , node_nm
      , depth
      , leaf_yn
  FROM  node_info 
 WHERE  1=1
<choose>
    <when test='node_id neq null and node_id neq ""'> 
       AND  pnode_id = #{node_id}
    </when>
    <otherwise>  
       AND  pnode_id = 1
    </otherwise>
</choose>
 
 
 cs

* 8~15line : node_id 가 없는 경우, 아무것도 선택되지 않은 경우(화면에 최초 진입한 상태)이며 최고 조상이라 할 수 있는 node_id 1 을 기준으로 자식 노드를 구한다

 

3-2. 선택된 셀렉트박스 값들을 기준으로 가이드 목록을 조회한 결과

1) 마지막 셀렉트박스가 특정 값으로 선택된 경우 : 단순 join 으로 해결

2) 마지막 셀렉트박스가 '전체'로 선택된 경우 : 아래와 같이 해결

 

ORACLE 은 CONNECT BY 함수를 사용하여 트리구조의 데이터를 출력 할 수 있겠지만,

MYSQL 은 CONNECT BY 함수를 지원하지 않는다.

 

다음과 같이 특정 노드 밑에 있는 모든 자손, 특정 노드 상위에 있는 모든 조상 노드를 구할 수 있다.

 

1. 특정 노드의 자손 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT node_id
     , pnode_id
     , depth
     , leaf_yn
     , node_path
 FROM  (
        SELECT * 
          FROM node_info
      ORDER BY pnode_id, node_id
        ) node_sorted,
       (SELECT @pv := #{node_id}) initial
WHERE   FIND_IN_SET(pnode_id, @pv)
  AND   LENGTH(@pv := concat(@pv, ',', node_id))
 cs

 

2. 특정 노드의 조상 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT   nid AS node_id
       , pnode_id
       , leaf_yn
       , depth
       , node_path
  FROM (
        SELECT  @nid AS nid,
              (
               SELECT  @nid := pnode_id
                 FROM  node_info
                WHERE  node_id = nid
               ) AS pnode_id
               , leaf_yn
               , depth
               , node_path
          FROM  (
                 SELECT  @nid := #{node_id}
                ) vars
                , node_info
         WHERE  @nid <> 0
        ) as tmp
 cs
참조 : https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

 

3. 특정 분류 밑의 모든 가이드 목록 조회 (특정 노드아이디 기준 모든 자손 노드를 구하는 1번 쿼리를 sub query로 사용)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  seq, 
        title, 
        (SELECT node_path FROM node_info where node_id = gi.guide_id) as guide_path
  FROM  guide_info gi
 WHERE  1=1
   AND  gi.guide_id IN  (SELECT * FROM (
                                             SELECT   node_id
                                               FROM  (SELECT node_id, pnode_id
                                                        FROM node_info
                                                    ORDER BY pnode_id, node_id
                                                     ) node_sorted,
                                                     (select @pv := #{node_id}) initial
                                              WHERE   FIND_IN_SET(pnode_id, @pv)
                                                AND   LENGTH(@pv := concat(@pv, ',', node_id))
                            ) as temp
                            )
ORDER BY seq
 cs

 

※ 위와 같이 subquery 를 사용하여 sql 에서 처리하지 않고, 소스레벨에서 처리하고 싶은 경우 foreach 를 사용하여 다음과 같이 처리할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT   gi.title, gi.guide_id, gi.text_item_1, ni.node_path AS rs
  FROM   guide_info gi
       , node_info ni
 WHERE   1=1
   AND   ni.node_id = gi.guide_id
<if test = 'sch_word neq null and sch_word neq ""'>
   AND   title LIKE CONCAT('%',#{sch_word},'%')
</if>
<if test = 'totalSearchFlag neq null and totalSearchFlag neq ""'>
  <choose>
     <when test = 'totalSearchFlag eq "Y"'>
      AND   ni.node_id IN 
       <foreach item="node_id_grp" index="i" collection="node_id_grp" separator="," open="(" close=")">
        #{node_id_grp}
       </foreach>
      </when>
      <otherwise>
        AND   ni.node_id = #{node_id}
      </otherwise>
  </choose>
</if>
 
 cs

* totalSearchFlag 는 selectBox 에서 '전체'가 선택되었음을 판별하기 위해 사용되는 값

* 1번 쿼리(특정노드의 모든 자손 구하기)로 뽑아온 모든 자손 노드를 필드명이 node_id_grp 인 List 자료형 에 담아 sql mapper 로 넘겨주어 사용

* open 은 for 문이 맨처음 돌기 전에, close 는 for문이 끝까지 돌고나서, separator 는 for 문이 한 번 씩 돌 때마다 적용된다.

* foreach(for문)로 작성된 쿼리 결과물은 다음과 같다

  (생략) ~ AND ni.node_id IN (2, 6)

 

 

 

위 쿼리를 사용해 가져온 데이터를 화면에 그려보자.

https://developyo.tistory.com/55?category=689339

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN  (4) 2019.11.14
SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
[Mysql] 시간 계산 : DATE_ADD, DATE_SUB  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27

1. 더하기

DATE_ADD(date, INTERVAL expr type)

DATE_ADD(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_ADD(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 더하기

DATE_ADD(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 빼기

DATE_ADD(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 더하기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_ADD(now(), INTERVAL -1 HOUR)
 cs

: 현재시각(now()) 에 -1 시간(HOUR)을 더한(DATE_ADD) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 더하면 뺄셈이 되므로 DATE_SUB 함수와 같이 동작한다.

 

 

2. 빼기

DATE_SUB(date, INTERVAL expr type)

DATE_SUB(날짜, INTERVAL 시간 시간타입)

 

ex) 

DATE_SUB(now(), INTERVAL 1 HOUR) : 현재 시각에 1시간 빼기

DATE_SUB(now(), INTERVAL -10 MINUTE) : 현재 시각에 10분 더하기

DATE_SUB(now(), INTERVAL 100 SECOND) : 현재 시각에 100초 빼기

1
2
3
4
SELECT  seq 
  FROM  hst_info
 WHERE  1=1
   AND  JOIN_DATE < DATE_SUB(now(), INTERVAL 1 HOUR)
 cs

: 현재시각(now()) 에 1 시간(HOUR)을 뺀(DATE_SUB) 값이 JOIN_DATE 보다 더 큰 hst_info 테이블의 seq 값을 조회

* expr 값으로 음수를 넣을 경우 음수를 빼는건 덧셈이 되므로( -(-1) = +1) DATE_ADD 함수와 같이 동작한다.

 

 

3. 기간 구하기

TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)

TIMESTAMPDIFF(시간타입, 시작시각, 끝시각)

 

ex)

TIMESTAMPDIFF(DAY, '20190820', now()) : 현재년월일 - 20190820 (%Y%m%d) 을 DAY 로 환산

TIMESTAMPDIFF(SECOND, '20190820140000', now()) : 현재년월일 - 20190820140000 (%Y%m%d%H%i%s) 을 SECOND 로 환산

1
2
3
SELECT seq
  FROM hst_info
 WHERE TIMESTAMPDIFF(SECOND, JOIN_DATE, now()) > 3600
 cs

: JOIN_DATE 시각 부터 현재시각(now()) 까지의 시간차이를 초(SECOND)로 환산한 값이 1시간(3600초) 보다 큰 hst_info 테이블의 seq 값을 조회

 

 

위와 같은 함수들을 사용하여

이력 및 통계를 뽑아내거나, 배치를 돌릴 때 시간을 기준으로 데이터를 조작할 수 있다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

 

 

* ORACLE 시간계산

https://oracle.tistory.com/216

 

 

 

반응형
저작자표시 비영리

'DB > MYSQL' 카테고리의 다른 글

SYSDATE vs NOW (SYSDATE 와 NOW 의 차이)  (0) 2019.11.02
Mysql hierarchy sql 계층구조 쿼리 : 설계 및 쿼리작성(Multi selectbox)  (0) 2019.08.21
SELECT + INSERT (mysql + mybatis)  (0) 2019.06.12
[Mysql/ORACLE] 여러 행을 한 줄로 표현하기(GROUP_CONCAT)  (0) 2019.05.27
mysql GROUP BY (only full group by)  (3) 2019.03.11

+ Recent posts

티스토리툴바