정규화된 테이블을 활용한 데이터베이스 관리

현대의 데이터 관리 시스템에서 데이터의 중복을 최소화하고 무결성을 유지하기 위해 데이터베이스 테이블을 정규화하는 과정은 필수적이다.

 

이번 포스트에서는 정규화된 테이블을 활용하여 데이터를 관리하고, SQL의 다양한 조인 기법을 이용해 데이터베이스에서 정보를 효과적으로 추출하는 방법을 살펴보자

 

테이블 생성 및 데이터 삽입

데이터베이스에서 정보를 저장하기 위해, 우선적으로 사용자 정보와 관련된 데이터를 저장할 member와 profile 테이블을 정의하고 예제 데이터를 삽입하자.

select * from member;# 전에 만든 member 테이블 확인

create table profile (
	userid varchar(20) not null,
    height double,
    weight double,
    mbti varchar(10),
    foreign key(userid) references member(userid)
);

desc profile;

insert into profile values ('apple',160,50,'ISTJ');
#insert into profile values ('grapes',180,80,'ESTP'); #member 테이블에 존재하지 않는 데이터
insert into profile values ('orange',170,70,'ISTJ');
insert into profile values ('avocado',180,80,'INTP');

select * from profile;

조인(Join)을 활용한 데이터 조회

조인(join)의 기본적인 구조

 

join

select 필드1, 필드2, ... from 테이블1[inner, left, right] join 테이블2 on 테이블1.필드 = 테이블2.필드

 

내부 조인(Inner Join)

두 테이블 간의 정확한 매칭 데이터만을 추출할 때 사용한다. 여기서는 member와 profile 테이블을 조인하여 사용자의 전체 정보를 조회한다.

select member.userid, name, gender, mbti from member join profile on member.userid = profile.userid;

첫 번째 쿼리에서는 SELECT 절에서 테이블 이름을 명시하지 않고 name, gender, mbti 필드를 선택하고 있습니다. 이 경우 SQL 엔진은 이 필드 이름들이 어떤 테이블에 속하는지 자동으로 인식해 반환한다.

 

select member.userid, member.name, member.gender, profile.mbti from member inner join profile on member.userid = profile.userid;

두 번째 쿼리에서는 각 필드 앞에 명시적으로 테이블 이름을 추가하여 (member.userid, member.name, member.gender, profile.mbti) 각 필드가 어느 테이블에 속하는지 명확히 지정하고 있다.

 

위와 같이 SQL 작성 시 일관성과 가독성을 위해 필드 앞에 테이블 이름을 명시할 수 있다.

 

LEFT JOIN과 RIGHT JOIN

left/right join의 기본적 구조
두 테이블이 조인 될 때 왼쪽(기본키 테이블) 또는 오른쪽(외래키 테이블) 기준으로, 기준 테이블의 데이터를 모두 출력

1. LEFT JOIN

LEFT JOIN (또는 LEFT OUTER JOIN)은 기본 테이블(왼쪽 테이블)의 모든 레코드와, 조인 조건에 맞는 오른쪽 테이블의 레코드를 결합한다. 오른쪽 테이블에 일치하는 레코드가 없는 경우, 해당 필드는 NULL로 채워진다.

select member.userid, member.name, member.gender, profile.mbti from member left join profile on member.userid = profile.userid;

이 쿼리는 member 테이블의 모든 사용자 정보와, 해당하는 profile 정보를 함께 조회한다. profile 테이블에 일치하는 userid가 없는 member 레코드는 mbti 값이 NULL로 표시된다.

2. RIGHT JOIN

RIGHT JOIN (또는 RIGHT OUTER JOIN)은 LEFT JOIN의 반대 방향으로 작동합니다. 오른쪽 테이블의 모든 레코드와 왼쪽 테이블의 일치하는 레코드를 결합합니다. 왼쪽 테이블에 일치하는 레코드가 없는 경우, 해당 필드는 NULL로 채워집니다.

select member.userid, member.name, member.gender, profile.mbti from member right join profile on member.userid = profile.userid;

이 쿼리는 profile 테이블의 모든 레코드를 기준으로, 해당하는 member 정보를 함께 조회한다. member 테이블에 일치하는 userid가 없는 profile 레코드는 name과 gender 값이 NULL로 표시된다.

테이블 별칭 사용 (AS 키워드)

AS 키워드를 사용하면 쿼리 내에서 테이블이나 컬럼에 임시 이름(별칭)을 지정할 수 있다. 이는 특히 조인이 복잡하거나 같은 테이블을 여러 번 사용할 때 유용하다.

select m.userid, m.name, m.gender, p.mbti from member as m left join profile as p on m.userid = p.userid;