DB2´Â ÀÌÁ¦ subselect ¹× fullselect¿¡¼ ORDER BY¸¦ Áö¿øÇÕ´Ï´Ù.
´ÙÀ½Àº order-by ÀýÀÇ À§Ä¡¸¦ Ç¥½ÃÇÏ´Â ¼öÁ¤µÈ fullselectÀÇ ºÎºÐ ±¸¹® ´ÙÀ̾î±×·¥ÀÔ´Ï´Ù.
>>-+-subselect---------+----------------------------------------> +-(fullselect)------+ '-| values-clause |-' .----------------------------------------------. V | >----+------------------------------------------+-+-------------> '-+-UNION---------+--+-subselect---------+-' +-UNION ALL-----+ +-(fullselect)------+ +-EXCEPT--------+ '-| values-clause |-' +-EXCEPT ALL----+ +-INTERSECT-----+ '-INTERSECT ALL-' >--+-----------------+----------------------------------------->< '-order-by-clause-'
ORDER BY ÀýÀ» Æ÷ÇÔÇÏ´Â fullselect¸¦ ´ÙÀ½¿¡¼ ÁöÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù.
fullselect¿¡¼ ORDER BY ÀýÀº Á¶È¸·Î ¸®ÅϵǴ ÇàÀÇ ¼ø¼¿¡ ¿µÇâÀ» ¹ÌÄ¡Áö ¾Ê½À´Ï´Ù. ORDER BY ÀýÀÌ °¡Àå ¿ÜºÎ fullselect¿¡¼ ÁöÁ¤µÇ´Â °æ¿ì ¸®ÅϵǴ ÇàÀÇ ¼ø¼¿¡¸¸ ¿µÇâÀ» ¹ÌĨ´Ï´Ù.
´ÙÀ½Àº order-by ÀýÀÇ À§Ä¡¸¦ Ç¥½ÃÇÏ´Â ¼öÁ¤µÈ subselectÀÇ Àüü ±¸¹® ´ÙÀ̾î±×·¥ÀÔ´Ï´Ù.
>>-select-clause--from-clause--+--------------+-----------------> '-where-clause-' >--+-----------------+--+---------------+-----------------------> '-group-by-clause-' '-having-clause-' >--+-----------------+----------------------------------------->< '-order-by-clause-'
subselect ÀýÀº ´ÙÀ½ ¼ø¼·Î 󸮵˴ϴÙ.
ORDER BY ÀýÀ» Æ÷ÇÔÇÏ´Â subselect¸¦ ´ÙÀ½¿¡¼ ÁöÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½Àº À¯È¿ÇÏÁö ¾Ê½À´Ï´Ù(SQLSTATE 428FJ SQLCODE -20211).
SELECT * FROM T1 ORDER BY C1 UNION SELECT * FROM T2 ORDER BY C1
´ÙÀ½ ¿¹´Â À¯È¿ÇÕ´Ï´Ù.
(SELECT * FROM T1 ORDER BY C1) UNION (SELECT * FROM T2 ORDER BY C1)
subselect¿¡¼ ORDER BY ÀýÀº Á¶È¸·Î ¸®ÅϵǴ ÇàÀÇ ¼ø¼¿¡ ¿µÇâÀ» ¹ÌÄ¡Áö ¾Ê½À´Ï´Ù. ORDER BY ÀýÀÌ °¡Àå ¿ÜºÎ fullselect¿¡¼ ÁöÁ¤µÇ´Â °æ¿ì ¸®ÅϵǴ ÇàÀÇ ¼ø¼¿¡¸¸ ¿µÇâÀ» ¹ÌĨ´Ï´Ù.
´ÙÀ½Àº ¼öÁ¤µÈ order-by ÀýÀÇ Àüü ±¸¹® ´ÙÀ̾î±×·¥ÀÔ´Ï´Ù.
.-,------------------------------. V .-ASC--. | >>-ORDER BY----+-sort-key--+------+---------+-+---------------->< | '-DESC-' | '-ORDER OF--table-designator-' sort-key |--+-simple-column-name--+--------------------------------------| +-simple-integer------+ '-sort-key-expression-'
ÀÌ ¾ç½ÄÀº fullselect(fullselectÀÇ ÅðÈ ¾ç½Ä°ú ´Ù¸¥)¿¡¼ Çã¿ëµÇÁö ¾ÊÀ½À» ÁÖÀÇÇϽʽÿÀ. ¿¹¸¦ µé¾î, ´ÙÀ½Àº À¯È¿ÇÏÁö ¾Ê½À´Ï´Ù.
(SELECT C1 FROM T1 ORDER BY C1) UNION SELECT C1 FROM T2 ORDER BY ORDER OF T1
´ÙÀ½ ¿¹´Â À¯È¿ÇÕ´Ï´Ù.
SELECT C1 FROM (SELECT C1 FROM T1 UNION SELECT C1 FROM T2 ORDER BY C1 ) AS UTABLE ORDER BY ORDER OF UTABLE
´ÙÀ½Àº ¼öÁ¤µÈ select¹®ÀÇ Àüü ±¸¹® ´ÙÀ̾î±×·¥ÀÔ´Ï´Ù.
>>-+-----------------------------------+--fullselect------------> | .-,-----------------------. | | V | | '-WITH----common-table-expression-+-' >--fetch-first-clause--*--+--------------------+----------------> +-read-only-clause---+ | (1) | '-update-clause------' >--*--+---------------------+--*--+--------------+------------->< '-optimize-for-clause-' '-WITH--+-RR-+-' +-RS-+ +-CS-+ '-UR-'
ÁÖ:
±¸¹®
.-,-------------. V | >>-select-clause--INTO----host-variable-+--from-clause----------> >--+--------------+--+-----------------+--+---------------+-----> '-where-clause-' '-group-by-clause-' '-having-clause-' >--+-----------------+--+--------------+----------------------->< '-order-by-clause-' '-WITH--+-RR-+-' +-RS-+ +-CS-+ '-UR-'
´ÙÀ½Àº ¼öÁ¤µÈ window-order ÀýÀ» Ç¥½ÃÇÏ´Â OLAP ÇÔ¼ö¿¡ ´ëÇÑ ºÎºÐ ±¸¹® ´ÙÀ̾î±×·¥ÀÔ´Ï´Ù.
window-order Àý .-,--------------------------------------------. V .-| asc option |--. | |--ORDER BY----+-sort-key-expression--+-----------------+-+-+---| | '-| desc option |-' | '-ORDER OF--table-designator---------------' asc ¿É¼Ç .-NULLS LAST--. |--ASC--+-------------+-----------------------------------------| '-NULLS FIRST-' desc ¿É¼Ç .-NULLS FIRST-. |--DESC--+-------------+----------------------------------------| '-NULLS LAST--'