SQL JOINS結合

● LEFT JOIN
A

SELECT <FIELD_LIST> 
FROM TABLEA A LEFT JOIN TABLEB B
ON A.PKEY = B.PKEY

A-B

SELECT <FIELD_LIST> 
FROM TABLEA A LEFT JOIN TABLEB B
ON A.PKEY = B.PKEY
WHERE B.PKEY IS NULL

● RIGHT JOIN
B

SELECT <FIELD_LIST> 
FROM TABLEA A RIGHT JOIN TABLEB B
ON A.PKEY = B.PKEY

B-A

SELECT <FIELD_LIST> 
FROM TABLEA A RIGTH JOIN TABLEB B
ON A.PKEY = B.PKEY
WHERE B.PKEY IS NULL

● OUTER JOIN
A∪B

SELECT <FIELD_LIST>
FROM TABLEA A FULL OUTER JOIN TABLEB B
ON A.PKEY = B.PKEY

A≒B−(A∏B):A≒BからA,Bの主キー部分を取り除く

SELECT <FIELD_LIST>
FROM TABLEA A FULL OUTER JOIN TABLEB B
ON A.PKEY = B.PKEY
WHERE A.PKEY IS NULL
AND B.PKEY IS NULL

● SELF JOIN
A∩B

SELECT <FIELD_LIST>
FROM TABLEA A INNER JOIN TABLEB B
ON A.PKEY = B.PKEY