FULL 조인은 2개의 테이블을 모든 합집합을 의미합니다. 2개의 테이블 좌우 모두의 데이 터를 출력합니다.
FULL 조인은 2개의 테이블 모두를 출력하기 때문에 큰 데이터 결과가 조회될 수 있습니다.
| 쿼리 문법 |
SELECT 컬럼1, 컬럼2, 컬럼3, . from 테이블1
LEFT JOIN
테이블2 ON 테이블1.컬럼 =테이블2.컬럼
UNION ALL
SELECT 컬럼1, 컬럼2, 컬럼3, …. from 테이블1
RIGHT JOIN
테이블2 ON 테이블1.컬럼 =테이블2.컬럼
20.5.1 쿼리 실습
FULL RIGHT 조인 명령을 다음과 같은 형태로 쿼리문을 작성할 수 있습니다.
| 예제 쿼리 |
SELECT * from members LEFT JOIN orders ON members.email=orders.email union all SELECT * from members RIGHT JOIN orders ON members.email=orders.email
20.5.2 PHP 실습
PHP 코드를 통하여 FULL 조인을 실습해 봅니다.
| PHP 예제 | mysql.class.php 파일에 메서드 예제를 추가합니다.
// FULL 조인을 처리합니다.
public function fullJoin($tbname1, $tbname2, $onWhere, $fields)
{
$queryString = "SELECT ";
// 컬럼필드 선택여부를 지정
if ($fields) {
$queryString .= $fields." ";
} else {
$queryString .= "* ";
}
$queryString = $queryString."from $tbname1 LEFT JOIN $tbname2 ON $onWhere"." union all ".$queryString."from $tbname1 RIGHT JOIN $tbname2 ON $onWhere";
if ($result = mysqli_query($this->dbcon, $queryString)) {
$this->msgEcho("쿼리성공] ".$queryString);
$rowss = "";
$row_cnt = mysqli_num_rows($result);
for ($i=0; $i<$row_cnt; $i++) {
$rowss[$i] = mysqli_fetch_object($result);
}
$result->free();
return $rowss;
} else {
$this->msgEcho("Error] ".$queryString);
}
}
예제 파일 | join-04.php
<?php
include "dbinfo.php";
include "mysql.class.php";
// ++ Mysqli DB 연결.
$db = new JinyMysql();
if ($rowss = $db->fullJoin("members","orders","members.email=orders.email")) {
echo "tables fields = ". count($rowss) . "<br>";
for ($i=0;$i<count($rowss);$i++) {
echo $i."=";
print_r($rowss[$i]);
echo "<br>";
}
}
?>
화면 출력
mysql connected!
쿼리성공] SELECT * from members LEFT JOIN orders ON members.email=orders.email union all SELECT * from members RIGHT JOIN orders ON members.email=orders.email
tables fields = 17
0=stdClass Object ( [Id] => 1 [LastName] => hojin [FirstName] => lee [Address] => shinchon [City] => seoul [Country] => Korea [manager] => infohojin [email] => hojin@jinyphp.com [code] => O_001 )
1=stdClass Object ( [Id] => 2 [LastName] => hojin [FirstName] => lee [Address] => shinchon [City] => seoul [Country] => Korea [manager] => infohojin [email] => hojin@jinyphp.com [code] => O_002 )
2=stdClass Object ( [Id] => [LastName] => lee [FirstName] => hojin [Address] => [City] => [Country] => Korea [manager] => infohojin [email] => [code] => )
3=stdClass Object ( [Id] => [LastName] => james [FirstName] => kim [Address] => youngdun-po [City] => seoul [Country] => Korea [manager] => info [email] => [code] => )
4=stdClass Object ( [Id] => [LastName] => 1234 [FirstName] => jiny [Address] => [City] => [Country] => Korea [manager] => [email] => [code] => )
5=stdClass Object ( [Id] => [LastName] => 1234 [FirstName] => jiny [Address] => [City] => [Country] => Korea [manager] => [email] => [code] => )
6=stdClass Object ( [Id] => [LastName] => 123400 [FirstName] => jiny [Address] => [City] => [Country] => Korea [manager] => [email] => [code] => )
7=stdClass Object ( [Id] => 1 [LastName] => hojin [FirstName] => lee [Address] => shinchon [City] => seoul [Country] => Korea [manager] => infohojin [email] => hojin@jinyphp.com [code] => O_001 )
8=stdClass Object ( [Id] => 2 [LastName] => hojin [FirstName] => lee [Address] => shinchon [City] => seoul [Country] => Korea [manager] => infohojin [email] => hojin@jinyphp.com [code] => O_002 )
9=stdClass Object ( [Id] => 3 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => eric@jinyphp.com [code] => O_003 )
10=stdClass Object ( [Id] => 4 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_004 )
11=stdClass Object ( [Id] => 5 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_005 )
12=stdClass Object ( [Id] => 6 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_006 )
13=stdClass Object ( [Id] => 7 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_007 )
14=stdClass Object ( [Id] => 8 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_008 )
15=stdClass Object ( [Id] => 9 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_009 )
16=stdClass Object ( [Id] => 10 [LastName] => [FirstName] => [Address] => [City] => [Country] => [manager] => [email] => [code] => O_010 )