[Database] join

JOIN

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것 (사용자가 필요한 집합 결과 도출)

→범위산정

 

INNER JOIN
OUTTER JOIN
SELF JOIN	거의 사용 안하지만 계층 구도 만들 때 사용
CROSS JOIN
NATURAL JOIN	같은 컬럼이어야 함. (=inner join과 같이 표현됨)

inner join

SELECT
	*
FROM
	tb_member_level AS l
	INNER join
	tb_member AS m
	on
	l.level_num = m.m_level
WHERE
	l.level_num > 1;

 

 

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level;

 

null 비교는 is 로!

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level
	WHERE
	m_id IS NULL;

 

 

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level
	WHERE
	m_id IS not null;

	

>> inner join과 결과값이 같다.

 

 

self join

SELECT
	m1.m_id AS member1
	,m2.m_id AS memeber2
	,m1.m_addr AS address
FROM
	tb_member AS m1
	join
	tb_member AS m2
	on
	m1.m_id <> m2.m_id
	and
	m1.m_addr = m2.m_addr
	ORDER BY  m1.m_id;

 

cross join

SELECT
	*
FROM
	tb_member_level AS l
	CROSS JOIN 
	tb_member AS m
ORDER BY l.level_num,m.m_id; 

A행xB행 (곱집합)