Mysql join 종류 및 예제를 통한 사용법
MySQL join 문은 사용자가 검색하고 싶은 칼럼이 다른 테이블에 있는 경우 주로 사용하며 여러 개의 테이블을 마치 하나의 테이블인 것처럼 활용하여 하나의 열로 표현하는 형식의 구문입니다.
MySQL에서 조인을 할 때, 본질적으로 두 테이블을 조인하여 두 테이블에서 데이터를 선택할 수 있습니다. 이것을 이해하는 가장 쉬운 방법은 실제로 그것을 해보는 것입니다. 그럼 mysql join 예제로 friends와 houses 테이블을 사용하여 설명해보도록 하겠습니다.
Mysql join 종류
Mysql join 문은 크게 두가지의 형태로 나누어집니다. Inner join과 Outer Join(Left Join, Right Join)으로 나뉘며, 먼저 Inner Join에 대한 설명입니다.
Mysql join 종류 – Inner Join
Join에는 여러 가지 유형이 있지만 Inner Join은 가장 일반적인 유형이며 기본적으로 교집합입니다. 지금까지의 모든 쿼리에선 Friends 또는 Houses 테이블을 독립적으로 쿼리 했습니다. 아래 테이블을 보겠습니다.
mysql> show tables;
+---------------------+
| Tables_in_addresses |
+---------------------+
| friends |
| houses |
+---------------------+
2 rows in set (0.00 sec)
mysql> describe friends;
+------------+----------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+------------+-------+
| first_name | varchar(40) | NO | PRI | | |
| last_name | varchar(40) | NO | PRI | | |
| cell_phone | varchar(20) | NO | | | |
| birthday | date | NO | PRI | 0000-00-00 | |
| house_id | smallint(5) unsigned | YES | | NULL | |
+------------+----------------------+------+-----+------------+-------+
5 rows in set (0.03 sec)
mysql> describe houses;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+----------------+
| house_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| address | varchar(120) | NO | UNI | | |
| home_telephone | varchar(25) | YES | | NULL | |
+----------------+----------------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)두 테이블에 동시에 있는 열이 있습니까? 맞습니다. 두 곳 모두에 존재하는 열은 “house_id“입니다. 조인 쿼리를 설정합니다. 그렇다면 왜 Inner join이라고 부를까요? 아래 이미지를 볼까요?
왼쪽의 원은 “Friends” 테이블이고 오른쪽의 원은 “Houses” 테이블입니다. 이 다이어그램은 둘 다 house_id 열을 공유하고 다이어그램 중간에 있으므로 Inner join이라고 합니다. 이제 Inner join을 사용하여 쿼리를 연습해봅시다. 몇 가지 알아 두어야 할 사항이 있으므로 몇 가지 쿼리를 완료한 다음 논의하겠습니다.
Friends 테이블에 친구를 몇 명 더 추가하겠습니다. 이 새로운 친구인 Heather와 Sandy는 자유로운 영혼입니다. 그들은 끊임없이 여행하며 집 주소가 없습니다. 따라서 그들의 집 ID는 null이 될 것입니다. 지금 데이터베이스에 추가해보겠습니다.
mysql> insert into friends set first_name='Sandy', last_name='Sholes'; Query OK, 1 row affected (0.00 sec) mysql> insert into friends set first_name='Heather', last_name='Jeffries'; Query OK, 1 row affected (0.00 sec)
Inner Join Using
먼저 Inner join을 테스트하기 위해 간단한 쿼리를 수행합니다. 사용할 수 있는 구문과 관련하여 몇 가지 접근 방식이 있습니다. 이 첫 번째 예에서는 using 키워드를 사용하겠습니다.
mysql> select first_name, last_name, address from friends
-> inner join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.03 sec)
위의 MySQL의 첫 번째 줄에서 friends 테이블과 houses 테이블에서 열을 선택합니다. 이것이 inner join입니다. 따라서 일반 영어로 쿼리를 읽으면 “Friends 또는 Houses 테이블에서 친구의 이름, 성 및 주소를 알려주고 집 ID를 공통 필드로 사용하십시오”와 같은 소리가 들릴 것입니다.
Inner Join On
이제 동일한 결과를 얻을 수 있지만 다른 구문을 사용하는 쿼리를 수행하겠습니다. 이것은 on 구문을 사용합니다.
mysql> select first_name, last_name, address from friends
-> inner join houses on friends.house_id = houses.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
동일한 결과를 얻었지만 쿼리는 약간 다르게 보였습니다. 우리는 그것을 더 잘 이해하기 위해 일반 영어로 읽을 수 있습니다. 쿼리는 Friends 테이블의 집 ID가 Houses 테이블의 집 ID와 동일한 조건으로 친구 및 집 테이블에서 친구의 이름, 성 및 주소를 알려줍니다.
Inner Join은 선택사항이 될 수 있습니다.
MySQL에서 Inner 부분을 지정하는 inner join은 실제로 선택 사항입니다. MySQL에서 명령문 을 작성할 때 어떤 Join 유형을 지정하지 않는다면 MySQL은 Inner join을 사용한다고 가정합니다. 증명하기 위해 그것을 시험해보겠습니다.
mysql> select first_name, last_name, address from friends
-> join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.03 sec)
mysql> select first_name, last_name, address from friends
-> join houses on friends.house_id = houses.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
위의 테이블에서 볼 수 있듯이 몇 번의 키 입력의 추가를 피하길 원한다면 위에서 말한 대로 이것은 잘 작동한다는 것을 확인할 수 있습니다.
테이블에서 별칭을 사용하여 Join 쿼리 단축
때로는 꽤 긴 테이블이 있을 수 있습니다. MySQL 쿼리에서, 특히 도트 스타일 구문에서 단 하나의 문자 만 보았고 그 점이 무엇인지 확실하지 않은 경우 사용 중인 Alias 유형일 가능성이 있습니다. 하나의 문자 만 사용하도록 별칭을 사용하여 테이블 이름을 줄일 수 있습니다. 아래와 같이 f는 Friends 테이블을 나타내고 h는 Houses 테이블을 나타냅니다.
mysql> select first_name, last_name, address from friends as f
-> inner join houses as h on f.house_id = h.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
두 테이블에서 모든 데이터 가져 오기
원하는 경우 두 테이블에서 모든 열을 한 번에 가져올 수 있습니다. select 문에서 별표 *를 사용하여 이것을 테스트하여 어떻게 작동하는지 봅시다. 더 좋은 형식을 위해 백 슬래시 대문자 G를 사용하여 MySQL 문을 종료할 수 있습니다.
mysql> select * from friends as f
-> inner join houses as h on f.house_id = h.house_idG
*************************** 1. row ***************************
first_name: Jim
last_name: Jones
cell_phone: 423-1423
birthday: 1985-07-24
house_id: 1
house_id: 1
address: 54 Main Street, Conway NH
home_telephone: 153-3487
*************************** 2. row ***************************
first_name: Sofia
last_name: Vergara
cell_phone: 487-4612
birthday: 1972-07-10
house_id: 2
house_id: 2
address: 742 Applewild, San Francisco CA
home_telephone: 487-1235
*************************** 3. row ***************************
first_name: Emmet
last_name: Brickowski
cell_phone: 987-1244
birthday: 2013-07-15
house_id: 3
house_id: 3
address: 836 Shark Ave, Chatham MA
home_telephone: 876-4576
*************************** 4. row ***************************
first_name: Dave
last_name: Chappelle
cell_phone: 977-5555
birthday: 1973-08-24
house_id: 4
house_id: 4
address: 88 Bundy Drive, Stowe VT
home_telephone: 746-9487
*************************** 5. row ***************************
first_name: Brenda
last_name: Penblanc
cell_phone: 719-4521
birthday: 1970-02-15
house_id: 5
house_id: 5
address: 87 Highcrest St, Orlando FL
home_telephone: 198-4723
*************************** 6. row ***************************
first_name: Jack
last_name: Penblanc
cell_phone: 581-1841
birthday: 1967-04-23
house_id: 5
house_id: 5
address: 87 Highcrest St, Orlando FL
home_telephone: 198-4723
*************************** 7. row ***************************
first_name: Frankie
last_name: Farmington
cell_phone: 978-1235
birthday: 1977-04-17
house_id: 6
house_id: 6
address: 92 Buzzfeed Lane, Denver CO
home_telephone: 258-4723
*************************** 8. row ***************************
first_name: Sheila
last_name: Farmington
cell_phone: 591-8522
birthday: 1977-07-12
house_id: 6
house_id: 6
address: 92 Buzzfeed Lane, Denver CO
home_telephone: 258-4723
*************************** 9. row ***************************
first_name: Jacob
last_name: Olivio
cell_phone: 512-3411
birthday: 1972-10-01
house_id: 7
house_id: 7
address: 12 Dunpoto Street, Boston MA
home_telephone: NULL
*************************** 10. row ***************************
first_name: Mary
last_name: Olivio
cell_phone: 412-9823
birthday: 1972-11-11
house_id: 7
house_id: 7
address: 12 Dunpoto Street, Boston MA
home_telephone: NULL
10 rows in set (0.00 sec)
Mysql join 종류 – Outer Join(Left Join , Right Join)
INNER JOIN 과 대비되면서 OUTER JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용합니다. 교집합이 아닌 부분도 같이 반환이 가능해집니다.
즉 A와 B 테이블을 JOIN 하는 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 OUTER JOIN을 사용합니다.
OUTER JOIN 을 사용하면 Left Join 또는 Right Join을 가질 수 있습니다.
Mysql join 종류 – Left Join
Left Join은 시각적 관점에서 아래 다이어그램과 같이 보입니다. 이것이 전달하려는 것은 friends 테이블의 모든 레코드를 표시하고 일치하는 Houses 테이블의 모든 항목과 일치한다는 것입니다. 일치하는 항목이 없는 경우 여전히 friends 테이블의 레코드를 확인하려고 합니다. 이것이 핵심 포인트이며 Inner join보다 덜 제한적입니다.
mysql> select first_name, last_name, address from friends
-> left join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Heather | Jeffries | NULL |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sandy | Sholes | NULL |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
12 rows in set (0.00 sec)
우리의 자유로운 열정적이고 노숙자(?)인 Heather와 Sandy가 이제 결과에 나타납니다. 이전 섹션에서 Inner join을 수행할 때 찾을 수 없었습니다.
Mysql join 종류 – Right Join
왼쪽 조인이 있는 것처럼 오른쪽 조인도 있습니다. Right Join은 다음과 같습니다. 이 경우 오른쪽이 우선합니다. 실제로 이러한 쿼리는 실제로 거의 동일한 작업을 수행합니다.
구문은 약간 바뀌었고 다음과 같습니다.
mysql> select first_name, last_name, address from houses
-> right join friends using(house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Heather | Jeffries | NULL |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sandy | Sholes | NULL |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
12 rows in set (0.00 sec)
Outer Join 필터링
Outer Join의 사용 사례가 궁금할 것입니다. 필터링을 위해 where 절과 결합할 때 Outer Join이 더 유용할 수 있습니다. 우리는 노숙자인 친구들만 볼 수도 있습니다.
mysql> select first_name, last_name, address from houses
-> right join friends using(house_id) where houses.address is null;
+------------+-----------+---------+
| first_name | last_name | address |
+------------+-----------+---------+
| Heather | Jeffries | NULL |
| Sandy | Sholes | NULL |
+------------+-----------+---------+
2 rows in set (0.00 sec)
이상으로 Mysql join 종류와 예제를 통한 사용법을 마치겠습니다.
MySQL INSERT UPDATE DELETE 방법에 대해 알아보자!
Comments are closed.