|DB2´Â ÀÌÁ¦ ORDER BY¸¦ subselects ¹× |fullselect¿¡¼ Áö¿øÇÕ´Ï´Ù.
|´ÙÀ½Àº 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--' | ||