[SQL] 목록 조회하기, Inner Join, outer join

 

 

📑 목록 조회하기

 

select 컬럼명 from t테이블명
where  조건
group by 속성명, 속성명
having 조건
order by 속성명 [ASC|DESC];

 

만약 컬럼명 앞에 distinct가 으면 중복된 튜플이 있을 때 그 중 첫 번째 한개만 표시한다. 

group by는 그룹함수를 사용할 때 쓴다. 

 

📌 그룹함수

count(속성명), sum(속성명), avg(속성명), max(속성명), min(속성명), stddev(속성명) :표준편차, variance(속성명) :분산 

rollup((속성명,속성명) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수, 속성 개수가 n개이면 n+1레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계된다. 

 

 

  select bo.bd_no
       ,bo.bd_title
       ,bo.loginID
       ,bo.bd_date
       ,bo.bd_cnt
    from tb_board bo

 

 

📌 CONTACT 사용하기

 

CONCAT 안에 넣으면 문자열 처리 가능

 

 select li_nm
           -- li_date,
           -- li_redate
           ,CONCAT(li_date, ' ~ ' , li_redate) 기간
      from tb_lecinfo;

 

 

데이터 안에 문자열 처리가 가능하며, 2개의 컬럼을 한 번에 출력할 수도 있다. 

 

 

 

📑 inner join 사용하기

 

Join은 2개의 릴레이션에서 연관된 튜플을 결합하여, 하나의 새로운 릴레이션을 반환한다. JOIN은 일반적으로 from 절에 기술하지만, 릴레이션이 사용되는 어느 곳 에서나 사요은 가능하다.

 

inner join은 대상 테이블에서 공통 속성을 기준으로 =(equla) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법이다. 즉) 교집합이라고 생각하면 된다. 

select 속성명 from 테이블명
inner join 테이블2
on 테이블1.속성명 = 테이블2.속성명

select 속성명 from 테이블명1, 테이블명2
where 테이블명1.속성명 = 테이블명2.속성명;

 

   -- ansi sql 사용
   select bo.bd_no
       ,bo.bd_title
       ,bo.loginID
       ,bo.bd_date
       ,bo.bd_cnt
    from tb_board bo
    inner join tb_userinfo ui
    on bo.loginID = ui.loginID;
    
    -- equi join 위의 2개와 결과 동일
     select bo.bd_no
       ,bo.bd_title
       ,bo.loginID
       ,bo.bd_date
       ,bo.bd_cnt
    from tb_board bo, tb_userinfo ui
    where bo.loginID = ui.loginID;

 

 

 

📑 outer join 사용하기

 

outer join은 릴레이션에 join 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법으로 left, right, full이 있다. 

차집합 + 교집합이라고 생각하거나 테이블 하나 전체 + 교집합이라 생각하면 될 것 같다. 만약 조인할 한족에 조건에 만족하는 값이 없어도 null을 추가한다. 

 

 

📌 left outer join

1. <ansi 표현법>
  select [테이블명1].속성명, [테이블명2].속성명 
   from 테이블명 1 
   left outer join 테이블명 2 
   on 테이블명1.속성명 = 테이블명2.속성명; 

2. select [테이블명1].속성명, [테이블명2].속성명 
    from 테이블명 1 , 테이블명 2
    where 테이블명1.속성명 = 테이블명2.속성명 (+) ;

 

ansi 표현법이란?

ANSI Join

SQL developer뿐만 아니라 현재 대부분의 상용 데이터베이스 시스템에서

표준 언어로 ansi (미국표준연구소) sql에서 제시한 표준 기능을 대부분 준수하고 있다.

ansi 표준 sql 조인 구문은 몇 가지 새로운 키워드와 절을 제공하여

select 문의 from 절에서 조인을 완벽하게 지정할 수 있다.

inner join 내부조인, outer join 외부조인

inner join 형식

select * from table1 inner join table2

on table1.column1 = table2.column2

예시

 

---

 

만약 과정명에서 a222를 클릭했을 때 아래 참여학생목록에 a222과정을 수강하고 있는  학생 이름과 학생의 시험 최종 점수를 출력하게 하고 싶다. 그럼 어떻게 해야할까???

 

 

 

🔨 1. 학생 테이블, 수강 목록 관리 테이블 inner join

 

 

 

tb_userinfo와 tb_subjectlist를 통해서 a 학생이 듣고 있는 강의 코드를 가져올 수 있고, inner join을 통해 사용자 정보의 학생 이름도 가져올 수 있다. 위의 사진에 참여 목록에 학생명을 출력해야 되기에 두개의 테이블을 join한 것. 

 

 

 select su.loginID
         ,su.li_no
         ,ui.name
    from tb_subjectlist su
          inner join tb_userinfo ui ON ui.loginID = su.loginID
    where li_no=1

1번 결과

 

🔨 2. 학생 답안지 테이블에서 학생의 점수 최대값 구하기

 

 

 

select li_no
       ,loginID
       ,sum(ss_score) score
  from tb_stdanswer
  group by li_no,loginID;

 

 

 

🔨 3. inner join과 outer join 합치기 

 

1번에서 학생 이름과 듣고 있는 강의 코드를 알아냈다. 이렇게 합쳐진 테이블에서, 우리는 학생에 해당하는 점수를 알아와야된다. 그러므로 왼쪽에 학생 컬럼을 기준으로 오른쪽에 점수를 출력해야한다. 이때 이용하는 게 outer join 

 

left outer join을 통해 학생 이름과 학생이 듣는 강의 정보를 알아냈다. 이 알아낸 정보를 통해 알아낸 정보의 login id와 li_no가 같은 조건으로 tb_stdanswer 테이블의 점수를 구하면 된다. 

  SELECT su.loginID
          ,su.li_no
          ,ui.name
          ,ifnull(ll.score,0) score 
     FROM tb_subjectlist su
          inner join tb_userinfo ui on ui.loginID = su.loginID
          left outer join (   
                               -- 서브쿼리 안에 적은 as는 밖에서 쓸 수 없다.  만약 tb_stdanswer st 라고 지정해 준다면! 
                              select li_no -- 강의 코드
                                    ,loginID
                                    , sum(ss_score) score
                                from tb_stdanswer
                               group by li_no,loginID 
                          ) ll on su.loginID = ll.loginID and su.li_no = ll.li_no
     WHERE su.li_no = 1;