데이터베이스 질문과 답변
WITH A AS ( SELECT 'A' AS A FROM DUAL UNION ALL SELECT 'B' AS A FROM DUAL UNION ALL SELECT 'C' AS A FROM DUAL ), B AS ( SELECT 'A' AS A, '1' AS B FROM DUAL UNION ALL SELECT 'A' AS A, '2' AS B FROM DUAL UNION ALL SELECT 'B' AS A, '1' AS B FROM DUAL UNION ALL SELECT 'C' AS A, '3' AS B FROM DUAL UNION ALL SELECT 'C' AS A, '4' AS B FROM DUAL ) SELECT A.A, B.B FROM A LEFT JOIN B ON A.A = B.A AND B.B = '1'
해당 쿼리 수행결과가 저는
| A | B |
| A | 1 |
| A | NULL |
| B | 1 |
| C | NULL |
| C | NULL |
로 예상을 했는데
| A | B |
| A | 1 |
| B | 1 |
| C | NULL |
이렇게 결과가 나오는데 왜 이렇게 나오는지 모르겠습니다.
OUTER JOIN 은 ON절에 조건 직접 주면 조건에 안맞을시 NULL로 나오는거 아닌가요??
by 마농
[2020.08.03 12:42:21]
ON 절에 2개 조건이 AND 로 연결되어 있는데.
- 잘못 생각한것 : ON 절은 조인조건으로 사용하고 AND 절은 필터조건으로 사용 (???)
- 실제 동작한것 : 2개 조건이 묶여서 하나의 조인 조건으로 사용됨
by 우리집아찌
[2020.08.03 13:10:04]
WITH A AS (
SELECT 'A' AS A FROM DUAL UNION ALL
SELECT 'B' AS A FROM DUAL UNION ALL
SELECT 'C' AS A FROM DUAL
), B AS (
SELECT 'A' AS A, '1' AS B FROM DUAL UNION ALL
SELECT 'A' AS A, '2' AS B FROM DUAL UNION ALL
SELECT 'B' AS A, '1' AS B FROM DUAL UNION ALL
SELECT 'C' AS A, '3' AS B FROM DUAL UNION ALL
SELECT 'C' AS A, '4' AS B FROM DUAL
)
SELECT A.A
, CASE WHEN B.B = '1' THEN '1' END
FROM A LEFT JOIN B
ON A.A = B.A
) 버튼을 클릭하여 작성 하시면 됩니다.