�択�

é?¸æŠžå¼?ã?®SELECT-FROM-WHEREã?‹ã‚‰ã?ªã‚‹è¨˜è¿°ã?«åŸºã?¥ã?„ã?¦ã€?å°„å½±ã?¨çµžè¾¼ã?¿ã?«ã‚ˆã‚Šè¡¨ç¾¤ã?‹ã‚‰æ–°ã?Ÿã?«è¡¨ã?®å€¤ã‚’å¾—ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚

æ§‹æ–‡

SELECT [ DISTINCT | ALL ] �択項目 [ , �択項目]*
FROM 節
[ WHERE 節 ]
[ GROUP BY 節 ]
[ HAVING 節 ]

�択項目:

{
    * |
    { 表å?? | 相関å?? } .* |
    å¼? [AS å?˜ç´”列å?? ]
}

SELECT節�����一覧�よ�FROM節�WHERE節��果��用�れる数�詞��り��。 DISTINCT�指定�れ�場���複��り除�れ��値を��行�一����り��ん。���NULL値�士���値�扱�れ��。も�数�詞����ALL�ら��果��行�値��複��も�り除�れ��ん。(既定�ALL����。)

�択項目�より一�以上��果�列��検索��得られる�果�表�射影�れ��。

FROM節ã?®è¨˜è¿°ã?«ã‚ˆã‚Šã€?節ã?«æ›¸ã?‹ã‚Œã?Ÿé …ç›®ã?®ç›´ç©?ã?Œçµ?æžœã?¨ã?—ã?¦ç”Ÿæˆ?ã?•れã?¾ã?™ã€‚ã?•らã?«WHERE節ã?«ã‚ˆã‚Šã€?ã??ã?®çµ?æžœã?¯é™?定ã?•れã?¾ã?™ã€‚

WHERE節��真���従��行�絞り込�れ��。��真�評価�れ�行����果���返�れ��。

GROUP BY節ã?«ã‚ˆã‚Šã€?一ã?¤ã?‚ã‚‹ã?„ã?¯ã??れ以上ã?®åˆ—ã?®é–“ã?§å?Œã?˜å€¤ã‚’æŒ?ã?¤è¡Œã?Œåˆ†é¡žã?•れã?¾ã?¨ã‚?られã?¾ã?™ã€‚GROUP BY節ã?¯å¤šã??ã?®å ´å?ˆã€?集約ã?Œä½µã?›ã?¦ä½¿ã‚?れã?¾ã?™ã€‚

GROUP BY節ã?Œã?‚ã‚‹å ´å?ˆã€?SELECT節ã?«ã?¯é›†ç´„ã?‹åˆ†é¡žåˆ—ã? ã?‘ç½®ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã‚‚ã?—分類ã?®åˆ—以外ã?®åˆ—ã‚’SELECT節ã?«èµ·ã??ã?Ÿã?„å ´å?ˆã€?ã??ã?®åˆ—を集約å¼?ã?®ä¸­ã?«ã?Šã?„ã?¦ã??ã? ã?•ã?„。例を挙ã?’ã?¾ã?™ã€‚

-- 員数�部署番�(WORKDEPT)�部�平�賃金(SALARY)�
-- をEMPLOYEE���表��る全��部署毎�一覧�る。
-- �果�部�平�賃金���昇順�並�られる。
SELECT WORKDEPT, AVG(SALARY)
     FROM EMPLOYEE
     GROUP BY WORKDEPT
     ORDER BY 1

GROUP BY節ã?Œã?ªã?„ã?«ã‚‚ã?‹ã?‹ã‚?らã?šã€?é?¸æŠžé …ç›®ã?«å‰¯å•?ã?„å?ˆã‚?ã?›ã?«ã?¯ã?ªã?„集約ã?Œã?‚ã‚‹å ´å?ˆã€?å•?å?ˆã?›ã?¯æš—é»™ã?«åˆ†é¡žã?•れã?¾ã?™ã€‚ã??ã?®ã?¨ã??表全体ã?Œä¸€ã?¤ã?«åˆ†é¡žã?•れã?¾ã?™ã€‚

HAVING節ã?«æ›¸ã?‹ã‚Œã?Ÿ(WHERE節ã?¨å?Œæ§˜ã?®)検索æ?¡ä»¶ã‚ˆã‚Šã€?分類ã?•れã?Ÿè¡¨ã?Œçµžã‚Šè¾¼ã?¾ã‚Œã?¾ã?™ã€‚ã?“ã?®æ¤œç´¢æ?¡ä»¶ã?¯åˆ†é¡žåˆ—ã?‚ã‚‹ã?„ã?¯å?¯è¦–ã?§ã?‚る集約ã?®ã?¿å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚HAVING節ã?¯è¡¨ã?‹ã‚‰åˆ†é¡žã?•れã?Ÿå¾Œã?®æƒ…å ±ã?«é?©ç”¨ã?•れã?¾ã?™ã€‚HAVING節ã?Œè©•価ã?•れã?¦çœŸã?§ã?‚ã?£ã?Ÿå ´å?ˆã€?ã??ã?®è¡Œã?¯æ®‹ã?£ã?¦ä»¥é™?ã?®å‡¦ç?†ã?Œè¡Œã‚?れã?¾ã?™ã€‚一方ã?§HAVING節ã?Œè©•価ã?•れã?¦å?½ã?‚ã‚‹ã?„ã?¯NULLã?§ã?‚ã?£ã?Ÿå ´å?ˆã€?ã??ã?®è¡Œã?¯ç ´æ£„ã?•れã?¾ã?™ã€‚ã‚‚ã?—HAVING節ã?Œã?‚ã‚‹ã?«ã‚‚ã?‹ã?‹ã‚?らã?šã€?GROUP BY節ã?Œã?ªã?„å ´å?ˆã€?æš—é»™ã?«è¡¨å…¨ä½“ã?Œä¸€ã?¤ã?«åˆ†é¡žã?•れã?¾ã?™ã€‚

Derby ��択�を次�順番�処����。
  • FROM 節
  • WHERE 節
  • GROUP BY (ã?¾ã?Ÿã?¯æš—é»™ã?®GROUP BY)
  • HAVING 節
  • SELECT 節

�択���果�常�表��。

å•?å?ˆã?›ã?«FROM節ã?Œã?ªã?„å ´å?ˆ(表ã?‹ã‚‰æƒ…報をå?–å¾—ã?™ã‚‹ã?®ã?§ã?¯ã?ªã??ã€?値を構æˆ?ã?™ã‚‹å ´å?ˆ)ã€?é?¸æŠžå¼?ã?§ã?¯ã?ªã??ã€?VALUESå¼?を使ã?„ã?¾ã?™ã€‚ 例:
VALUES CURRENT_TIMESTAMP

VALUESå¼?ã‚’å?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。

ワイルドカード�*

*�よりFROM節��る表�全列�展開�れ��。

表å??.* ã‚„ 相関å??.* ã?«ã‚ˆã‚Šã€?ã??ã?®è¡¨ã?®å…¨åˆ—ã?Œå±•é–‹ã?•れã?¾ã?™ã€‚ã??ã?®è¡¨ã?¯FROM節ã?«ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。

列ã?¸ã?®å‘½å??

AS節を使ã?£ã?¦é?¸æŠžé …ç›®ã?®åˆ—ã?«å??å‰?ã‚’ã?¤ã?‘る事ã?Œã?§ã??ã?¾ã?™ã€‚ã‚‚ã?—é?¸æŠžé …ç›®ã?Œå?˜ã?ªã‚‹åˆ—å?‚ç…§ã?§ã?¯ã?ªã?„ã?‹ã?‚ã‚‹ã?„ã?¯ã€?AS節ã?§å??å‰?ã?Œã?¤ã?‘られã?¦ã?„ã?ªã?„å ´å?ˆã€?自動生æˆ?ã?•れã?Ÿå??å‰?ã?Œä¸Žã?ˆã‚‰ã‚Œã?¾ã?™ã€‚

ã?“ã?†ã?„ã?£ã?Ÿåˆ—ã?®å??å‰?ã?¯ã€?ã?•ã?¾ã?–ã?¾ã?ªå ´å?ˆã?§ä¾¿åˆ©ã?«ä½¿ã?†ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
  • JDBCã?®ResultSetMetaDataã?«ã?¦åˆ©ç”¨å?¯èƒ½ã?§ã?™ã€‚
  • FROM節ã?®å‰¯å•?ã?„å?ˆã‚?ã?›ã?«ã?¦ã€?ã??ã?®é?¸æŠžå¼?ã?Œä½¿ã‚?れã?Ÿå ´å?ˆã€?çµ?果表ã?®åˆ—å??ã?¨ã?—ã?¦ä½¿ã‚?れã?¾ã?™ã€‚
  • ORDER BY節ã?«ã?¦ä¸¦ã?³æ›¿ã?ˆã‚’行ã?†åˆ—ã?®å??å‰?ã?¨ã?—ã?¦ä½¿ã‚?れã?¾ã?™ã€‚

-- ã?“ã?®ä¾‹ã?¯ORDER BY節ã€?表ã?¸ã?®ç›¸é–¢å??ã€?
-- SELECT-FROM-WHEREを示���。
SELECT CONSTRAINTNAME, COLUMNNAME 
FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks 
WHERE t.TABLENAME = 'FLIGHTS' AND t.TABLEID = col.
REFERENCEID AND t.TABLEID = cons.TABLEID 
AND cons.CONSTRAINTID = checks.CONSTRAINTID 
ORDER BY CONSTRAINTNAME
-- ��例�DISTINCT節�使�方を示���。
SELECT DISTINCT ACTNO
FROM EMP_ACT
-- ã?“ã?®ä¾‹ã?¯å¼?ã?«å??å‰?を与ã?ˆã‚‹æ–¹æ³•を示ã?—ã?¾ã?™ã€‚
-- EMPLOYEE���表�ら�最も高�給料�他�全部署�給料�平�より安�部署��
-- 部署番å?·(WORKDEPT)ã€?BOSSã?¨ã?„ã?†å??å‰?を与ã?ˆã?Ÿéƒ¨ç½²ã?®æœ€ã‚‚高ã?„給料(SALARY)ã‚’
-- 一覧���。
SELECT WORKDEPT AS DPT, MAX(SALARY) AS BOSS 
	FROM EMPLOYEE EMP_COR 
	GROUP BY WORKDEPT 
	HAVING MAX(SALARY) < (SELECT AVG(SALARY)
				FROM EMPLOYEE
				WHERE NOT WORKDEPT = EMP_COR.WORKDEPT) 
	ORDER BY BOSS
関連概念
動的パラメータ
関連資料
TableExpression
VALUESå¼?
��関�る優先順�
真��