DB/SQL/½¸¹ç±é»»»Ò¤ÎÍøÍÑ
½¸¹ç±é»»»Ò¤Î¼ïÎà†
½¸¹ç±é»»»Ò¤Ï¡¢Ê£¿ô¤Î SELECT ʸ¤Î·ë²Ì¤ÎϽ¸¹ç¡¢Àѽ¸¹ç¡¢º¹½¸¹ç¤òµá¤á¤ë¤¿¤á¤Î¤â¤Î¡£
½¸¹ç±é»»»Ò¤Ë¤Ï°Ê²¼¤Î¼ïÎब¤¢¤ë¡£
±é»»»Ò | ÀâÌÀ |
union | ½ÅÊ£¤ò½ü¤¤¤¿Ï½¸¹ç¤òµá¤á¤ë |
union all | ½ÅÊ£¤ò´Þ¤àϽ¸¹ç¤òµá¤á¤ë |
intersect | Àѽ¸¹ç¤òµá¤á¤ë |
minus | º¹½¸¹ç¤òµá¤á¤ë |
UNION†
Æó¤Ä¤Î SELECT ʸ¤Î·ë²Ì¤ÎϽ¸¹ç¤òµá¤á¤ë¡£½ÅÊ£¹Ô¤Ï½ü¤«¤ì¤ë¡£
SELECT ʸ¤Î°ìÈֺǽé¤ÎÎó¤Ç¥½¡¼¥È¤µ¤ì¤Æ½ÐÎϤµ¤ì¤ë¡£
- ÊýË¡
-- ´ðËÜ select hoge, fuga from piyo union select foo, bar from buz -- ·¿¤ò̵Íý¤ä¤ê¹ç¤ï¤»¤ë¤Ê¤é¤³¤¦ select hoge, to_date(null), fuga from piyo union select foo, bar, to_number(null) from baz
- ÊäÂ
- NULL ¤Ï½ÅÊ£¤Î¥Á¥§¥Ã¥¯¤Ç¤Ï̵»ë¤µ¤ì¤ë
- ³Æ SELECT ʸ¤ÎÎó¤Î¿ô¤È·¿¤Ï°ì½ï¤Ç¤Ê¤¤¤È¥À¥á¡£ÌµÍý¤ä¤ê¤ä¤ë¤Ê¤éÎã¤ò»²¾È
UNION ALL†
Æó¤Ä¤Î SELECT ʸ¤Î·ë²Ì¤ÎϽ¸¹ç¤òµá¤á¤ë¡£½ÅÊ£¹Ô¤â½ÅÊ£¤·¤¿¤À¤±½ÐÎϤµ¤ì¤ë¡£
¥½¡¼¥È¤Ï¤µ¤ì¤Ê¤¤¡£
- ÊýË¡
-- ´ðËÜ select hoge, fuga from piyo union all select foo, bar from buz
- ÊäÂ
- ³Æ SELECT ʸ¤ÎÎó¤Î¿ô¤È·¿¤Ï°ì½ï¤Ç¤Ê¤¤¤È¥À¥á¡£ÌµÍý¤ä¤ê¤ä¤ë¤Ê¤é UNION ¤ÎÎã¤ò»²¾È
INTERSECT†
Æó¤Ä¤Î SELECT ʸ¤Î·ë²Ì¤ÎÀѽ¸¹ç¤òµá¤á¤ë¡£¶¦ÄÌÉôʬ¡£
¥½¡¼¥È¤Ï¤µ¤ì¤Ê¤¤¡£
- ÊýË¡
-- ´ðËÜ select hoge, fuga from piyo intersect select foo, bar from buz
- ÊäÂ
- NULL ¤Ï̵»ë¤µ¤ì¤Ê¤¤¡£¹Í褵¤ì¤ë
- ³Æ SELECT ʸ¤ÎÎó¤Î¿ô¤È·¿¤Ï°ì½ï¤Ç¤Ê¤¤¤È¥À¥á¡£ÌµÍý¤ä¤ê¤ä¤ë¤Ê¤é UNION ¤ÎÎã¤ò»²¾È
MINUS†
Æó¤Ä¤Î SELECT ʸ¤Î·ë²Ì¤Îº¹½¸¹ç¤òµá¤á¤ë¡£
½çÈÖ¤¬Âç»ö¡£¤Ò¤È¤Ä¤á¤Î SELECT ¤Î·ë²Ì¤«¤é¡¢¤Õ¤¿¤Ä¤á¤Î SELECT ¤Î·ë²Ì¤¬½ü¤«¤ì¤ë¡£µÕ¤Ë¤¹¤ì¤Ð·ë²Ì¤¬ÊѤï¤ë¡£
- ÊýË¡
-- ´ðËÜ select hoge, fuga from piyo minus select foo, bar from buz
- ÊäÂ
- WHERE ¶ç¤ò»È¤¦¾ì¹ç¤Ï¡¢WHERE ¶ç¤Ë»È¤¦¤¹¤Ù¤Æ¤ÎÎó¤ò¸µ¤Î SELECT ʸ¤Ë´Þ¤á¤ëɬÍפ¬¤¢¤ë
- ³Æ SELECT ʸ¤ÎÎó¤Î¿ô¤È·¿¤Ï°ì½ï¤Ç¤Ê¤¤¤È¥À¥á¡£ÌµÍý¤ä¤ê¤ä¤ë¤Ê¤é UNION ¤ÎÎã¤ò»²¾È
½ç½ø¤ÎÀ©¸æ†
ɽ¼¨½ç¤ò SELECT ʸ¤Î½ç½ø¤Ë¹ç¤ï¤»¤¿¤¤¤È¤¤Ê¤É¤Ë»È¤¦¥Æ¥¯¥Ë¥Ã¥¯¡£NOPRINT ¤ò»ØÄꤷ¤¿¥À¥ß¡¼¤ÎÎó¤ËǤ°Õ¤ÎÀ°ÎóÍѤÎÃͤòÍ¿¤¨¤Æ¡¢¤½¤ì¤Ç ORDER BY ¤¹¤ë¡£
- ÊýË¡
-- ¤Þ¤ºÈóɽ¼¨¤Ë¤¹¤ë¥½¡¼¥ÈÍѤÎÎó¤ò»ØÄꤹ¤ë colmun fuga noprint -- ³Æ SELECT ʸ¤Ë¡¢Ç¤°Õ¤ÎÃͤò»ý¤¿¤»¤¿¥À¥ß¡¼¤ÎÎó¤òÍ¿¤¨¤ë select hoge, 10 fuga from piyo union select foo, 20 fuga from baz order by fuga
Last-modified: 2011-10-17 (·î) 20:05:29