SQL SELECT 조건절 / WHERE 조건절 / JOIN

쿼리 현행화중.. 결과는 같지만 기독성을 좋게하거나 속도를 위해 조건절을 활용하여 쿼리를 수정하고있다.

 

SELECT 조건절 

: select 에서 조건을 바로 준다.

SELECT
	col1
    ,(SELECT bcol1 FROM Btable WHERE bid = a.aid) col2
    ,col3
 FROM Atable
WHERE col1 IS NOT NULL

WHERE 조건절

: where에서 조건을 준다.

SELECT
	A.col1
    ,B.bcol1 as col2
    ,A.col3
 FROM Atable A , Btable B
WHERE A.aid = B.bid
	AND col1 IS NOT NULL

 

======

아래는 SELECT 조건절 - > LEFT OUTHER JOIN 로 바꿔보았다.

SELECT T.ST_DT
		,T.CUST_NO
        ,T.UP_1_NO
        ,T.UP_1_NM
        ,T.UP_1_CD
        ,T.UP_2_NO
        ,T.UP_2_NM
        ,T.UP_2_CD
  FROM
   (SELECT TO_CHAR(ENTR_DT, 'YYYYMMDD') ST_DT
   		,CUST_NO
        ,(SELECT DISTINCT UP_NO FROM BTABLE WHERE BID=A.AUP_ID_1) UP_1_NO
        ,(SELECT DISTINCT UP_NM FROM BTABLE WHERE BID=A.AUP_ID_1) UP_1_NM
        ,(SELECT DISTINCT UP_CD FROM BTABLE WHERE BID=A.AUP_ID_1) UP_1_CD
        ,(SELECT DISTINCT UP_NO FROM BTABLE WHERE BID=A.AUP_ID_2) UP_2_NO
        ,(SELECT DISTINCT UP_NM FROM BTABLE WHERE BID=A.AUP_ID_2) UP_2_NM
        ,(SELECT DISTINCT UP_CD FROM BTABLE WHERE BID=A.AUP_ID_2) UP_2_CD
    FROM ATABLE A
  WHERE CUST_NO IS NOT NULL
  ) T
 WHERE (UP_1_NO > ' ' OR UP_2_NO > ' ')
 	AND (UP_1_NM > ' ' OR UP_2_NM > ' ')
    AND (UP_1_CD > ' ' OR UP_2_CD > ' ')

SELECT A.ST_DT
		,A.CUST_NO
        ,B1.UP_NO UP_1_NO
        ,B1.UP_NM UP_1_NM
        ,B1.UP_CD UP_1_CD
        ,B2.UP_NO UP_2_NO
        ,B2.UP_NM UP_2_NM
        ,B2.UP_CD UP_2_CD
  FROM ATABLE A
  		LEFT OUTER JOIN BTABLE B1 ON (B1.BID=A.AUP_ID_1)
        LEFT OUTER JOIN BTABLE B2 ON (B1.BID=A.AUP_ID_2)
 WHERE CUST_NO IS NOT NULL
 	AND (UP_1_NO > ' ' OR UP_2_NO > ' ')
 	AND (UP_1_NM > ' ' OR UP_2_NM > ' ')
    AND (UP_1_CD > ' ' OR UP_2_CD > ' ')