DB/SQL/Ê£¿ô¤Îɽ¤Î·ë¹ç
NATURAL JOIN†
¼«Á³·ë¹ç¡£¥Ç¡¼¥¿¥Ù¡¼¥¹Â¦¤Ë¡Ø¤³¤ì¤È¤³¤ì¤¯¤Ã¤Ä¤±¤¿¤¤¡£¤¢¤È¤Ï¤¦¤Þ¤¤¤³¤È¤ä¤Ã¤Æ¤¯¤ì¡Ù¤È¤ª´ê¤¤¤¹¤ë·ë¹çÊýË¡¡£Åù²Á·ë¹ç¤Î¤ß¡£ÆâÉô·ë¹ç¡£
Æó¤Ä¤Îɽ¤Ç¡¢·ë¹ç¤·¤¿¤¤Îó¤ÎÎó̾¤È¥Ç¡¼¥¿·¿¤¬°ìÃפ·¤Æ¤¤¤ëɬÍפ¬¤¢¤ë¡£µÕ¤Ë¤¤¤¨¤Ð¡¢Îó̾¤È¥Ç¡¼¥¿·¿¤¬°ìÃפ·¤Æ¤¤¤ëÎó¤Ç¾¡¼ê¤Ë·ë¹ç¤µ¤ì¤ë¡£
- ÊýË¡
-- hoge ɽ¤È foo ɽ¤ò·ë¹ç¤¹¤ë select fuga, piyo, bar from hoge natural join foo;
- ÊäÂ
- ɽÊÌ̾¡¢ÎóÊÌ̾¤Ï»È¤Ã¤Æ¤â¤¤¤¤¤±¤É¡¢·ë¹ç¤µ¤ì¤ëÎó¤Ë¤Ï½¤¾þ»Ò¤Ï»È¤¨¤Ê¤¤¡£¤¿¤È¤¨¤Ð¡¢¾å¤ÎÎã¤Ç hoge ɽ¤È foo ɽ¤¬ fuga Îó¤Ç¼«Á³·ë¹ç¤µ¤ì¤ë¤È¤¡¢from hoge h ¤Ê¤É¤ÎɽÊÌ̾¤ÏÉÕ¤±¤Æ¤â¤¤¤¤¤¬¡¢select h.fuga ¤Ê¤É¡¢·ë¹çÎó¤Ë¤Ï½¤¾þ»Ò¤òÉÕµ¤Ç¤¤Ê¤¤¡¢¤È¤¤¤¦¤³¤È
JOIN USING†
¼«Á³·ë¹ç¤À¤È°Õ¿Þ¤·¤Ê¤¤Îó¤Þ¤Ç·ë¹ç¤µ¤ì¤Æ¤·¤Þ¤¦¤È¤¡¢¤â¤·¤¯¤ÏÎó̾¤Ï°ìÃפ·¤Æ¤¤¤ë¤±¤É¥Ç¡¼¥¿·¿¤¬°ã¤¦¤È¤¤Ë»È¤¦·ë¹çÊýË¡¡£Åù²Á·ë¹ç¤Î¤ß¡£ÆâÉô·ë¹ç¡£
Æó¤Ä¤Îɽ¤Ç¡¢·ë¹ç¤·¤¿¤¤Îó¤ÎÎó̾¤¬°ìÃפ·¤Æ¤¤¤ëɬÍפ¬¤¢¤ë¡£¥Ç¡¼¥¿·¿¤Î°ìÃפϡÊÁê¸ß¤ËÊÑ´¹²Äǽ¤Ê¡¢¸ß´¹À¤Î¤¢¤ëÁȤ߹ç¤ï¤»¤Ç¤¢¤ì¤Ð¡ËÉÔÍס£
- ÊýË¡
-- hoge ɽ¤È foo ɽ¤ò piyo Îó¤Ç·ë¹ç¤¹¤ë select fuga, piyo, bar from hoge join foo using (piyo);
- ÊäÂ
- ·¿ÊÑ´¹¤Ï¡¢¸«¤¿´¶¤¸¤Ç¤¤½¤¦¤À¤Ã¤¿¤é¤À¤¤¤¿¤¤¤Ç¤¤ë¤Ã¤Ý¤¤¡£Ê¸»úÎó¤È¤·¤Æ¿ô»ú¤¬Æþ¤Ã¤Æ¤¤¤ë varchar2 ·¿¤È number ·¿¤È¤«¡¢date ·¿¤È date ·¿¤Î¥Ç¥Õ¥©¥ë¥È¤Î¥Õ¥©¡¼¥Þ¥Ã¥È¤Çʸ»úÎ󤬳ÊǼ¤µ¤ì¤Æ¤¤¤ë varchar2 ·¿¤È¤«
- ɽÊÌ̾¡¢ÎóÊÌ̾¤Î»ÈÍѥ롼¥ë¤Ï NATURAL JOIN ¤ÈƱ¤¸¡£·ë¹çÎó¤Ë¤Ï½¤¾þ»Ò¤ÏÉղäǤ¤Ê¤¤
JOIN ON†
·ë¹ç¾ò·ï¤òÌÀ¼¨¤¹¤ë·ë¹çÊýË¡¡£¤À¤¤¤¿¤¤²¿¤Ç¤â¤Ç¤¤ë¡£Åù²Á·ë¹ç¤âÉÔÅù²Á·ë¹ç¤â¤Ç¤¤ë¡£ÆâÉô·ë¹ç¡£
·ë¹ç¤·¤¿¤¤Îó¤Î̾Á°¤â¥Ç¡¼¥¿·¿¤â¡¢°ìÃפ·¤Æ¤¤¤ëɬÍפϤʤ¤¡Ê¥Ç¡¼¥¿·¿¤ÏÁê¸ß¤ËÊÑ´¹²Äǽ¤Ç¤¢¤ëɬÍפϤ¢¤ë¡Ë¡£
- ÊýË¡
-- hoge ɽ¤È foo ɽ¤òÁÐÊý¤Î piyo Îó¤Ç·ë¹ç¤¹¤ë select h.fuga, h.piyo, f.bar from hoge h join foo f on (h.piyo = f.piyo); -- ÉÔÅù²Á·ë¹ç¤ÎÎã¤Ï¤³¤ó¤Ê select h.fuga, h.piyo, f.bar from hoge h join foo f on (h.piyo between f.puni and f.bar);
- ÊäÂ
- ON ¤Î³ç¸Ì¤ÎÃæ¤Ë¤¤¤í¤¤¤í½ñ¤±¤Ð¤¤¤í¤¤¤í¤Ç¤¤ë
OUTER JOIN†
³°Éô·ë¹ç¤ò¤·¤¿¤¤¤È¤¤Ï OUTER ¶ç¤ò»ØÄꤹ¤ë¡£¾å¤Ç½ñ¤¤¤¿¤¤¤í¤¤¤í¤Ê¥Ñ¥¿¥ó¤Ë¤â¤½¤ì¤¾¤ìŬÍѲÄǽ¡£
- ÊýË¡
-- º¸Â¦³°Éô·ë¹ç¡Êº¸¤Îɽ¤Î¥ì¥³¡¼¥É¤ÏÁ´·ï½ÐÎϤ¹¤ë¡Ë select h.fuga, h.piyo, f.bar from hoge h left outer join foo f on (h.piyo = f.piyo); -- ±¦Â¦³°Éô·ë¹ç¡Ê±¦¤Îɽ¤Î¥ì¥³¡¼¥É¤ÏÁ´·ï½ÐÎϤ¹¤ë¡Ë select h.fuga, h.piyo, f.bar from hoge h right outer join foo f on (h.piyo = f.piyo); -- ´°Á´³°Éô·ë¹ç¡Ê¤É¤Á¤é¤Îɽ¤Î¥ì¥³¡¼¥É¤âÁ´·ï½ÐÎϤ¹¤ë¡Ë select h.fuga, h.piyo, f.bar from hoge h full outer join foo f on (h.piyo = f.piyo);
CROSS JOIN†
¥Ç¥«¥ë¥ÈÀÑ¡£¤Ä¤Þ¤ë¤È¤³¤í¤Î¡¢Æó¤Ä¤Îɽ¤ÎÁ´ÁȤ߹ç¤ï¤»¤Î½ÐÎÏ¡£
·ë¹ç¾ò·ï¤ò¾Êά¤·¤¿¤ê¡¢·ë¹ç¾ò·ï¤¬Ìµ¸ú¤À¤Ã¤¿¤ê¤¹¤ë¤È¤³¤ì¤Ë¤Ê¤ë¡£ÌÀ¼¨Åª¤Ë¤³¤ì¤ò¤¹¤ë¾ì¹ç¤Ï CROSS JOIN ¶ç¤òÍѤ¤¤ë¡£
- ÊýË¡
select fuga, piyo, bar from hoge cross join foo;
JOIN ¤ò»È¤ï¤Ê¤¤·ë¹ç†
ÌÀ¼¨Åª¤Ë JOIN ¶ç¤òµ½Ò¤·¤Ê¤¯¤Æ¤â¡¢É½ÊÌ̾¤È WHERE ¶ç¤ÎÁȤ߹ç¤ï¤»¤Ç·ë¹çɽ¤Ï½ÐÎϲÄǽ¡£
- ÊýË¡
-- ñ¤Ê¤ëÆâÉô·ë¹ç select h.fuga, h.piyo, f.bar from hoge h, foo f where h.piyo = f.piyo; -- º¸Â¦³°Éô·ë¹ç¡ÊOracle Æȼ«¡Ë select h.fuga, h.piyo, f.bar from hoge h, foo f where h.piyo = f.piyo(+); -- ±¦Â¦³°Éô·ë¹ç¡ÊOracle Æȼ«¡Ë select h.fuga, h.piyo, f.bar from hoge h, foo f where h.piyo(+) = f.piyo; -- ´°Á´³°Éô·ë¹ç¤Ï FULL OUTER JOIN ¤Ç¤Ê¤¤¤È½ñ¤±¤Ê¤¤
抠
- ²Äǽ¤Ç¤¢¤ì¤Ð¡¢É½ÊÌ̾¤ÏÉÕ¤±¤¿¤Û¤¦¤¬Îɤ¤¡£¥Ñ¥Õ¥©¡¼¥Þ¥ó¥¹¤â¸þ¾å¤¹¤ë¤·¡¢²ÄÆÉÀ¤â¾å¤¬¤ë
- WHERE¤Ë¤è¤ë¹Ô¤ÎÀ©¸Â¤¬²Äǽ
- 3¤Ä°Ê¾å¤Îɽ¤â·ë¹ç²Äǽ¡£·ë¹ç½ç½ø¤Ïº¸¤«¤é½ç¤Ë¤Ê¤ë
- ¥Ñ¥Õ¥©¡¼¥Þ¥ó¥¹¤ò¹Í¤¨¤ë¾å¤Ç¤Ï·ë¹ç½ç½ø¤âÂç»ö¡£¤¿¤È¤¨¤Ð 1000 Ëü¹Ô¤Îɽ + 100 ¹Ô¤Îɽ + 10 ¹Ô¤Îɽ + ¡Ä¡Ä ¤È¤·¤¿¤È¤¤Ï 1000 Ëü¹Ô¤òºÇ¸å¤Ë¤·¤¿¤Û¤¦¤¬Áᤤ
Last-modified: 2011-10-17 (·î) 20:05:03