Fortis Central Forum
January 18, 2020, 10:02:23 AM *
Welcome, Guest. Please login or register.

Login with username, password and session length
News: Brotherhood above all circumtances  -  Fortis Central is Unity
   Home   Help Search Login Register  
Pages: [1]   Go Down
Author Topic: Oracle/PLSQL: Subqueries  (Read 3841 times)
0 Members and 1 Guest are viewing this topic.
Executive Manager

Karma: +0/-0
Offline Offline

Posts: 268

Owen Ario Hendrawan, S.KOM

View Profile
« on: June 24, 2010, 11:57:45 AM »


SUBQUERY adalah Query di dalam Query. Artinya kita dapat membuat beberapa SUBQUERY sekaligus dalam 1 Query.
SUBQUERY ini dapat diletakkan pada WHERE clause, FROM clause, dan SELECT clause.

WHERE clause

Adalah SUBQUERY yang paling umum digunakan. SUBQUERY ini disebut juga NESTED SUBQUERY


select * from all_tables tabs where tabs.table_name in
(select cols.table_name from all_tab_columns cols where cols.column_name = 'SUPPLIER_ID');

Oracle memiliki kemampuan 255 level SUBQUERY pada WHERE clause.

FROM clause

SUBQUERY pada FROM clause. Disebut juga INLINE VIEWS.


select, subquery1.total_amt from suppliers,
(select supplier_id, Sum(orders.amount) as total_amt from orders) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;

Oracle memiliki kemampuan tidak terbatas untuk SUBQUERY pada FROM clause.

SELECT clause


select tbls.owner, tbls.table_name,
(select count(column_name) as total_columns from all_tab_columns cols where cols.owner = tbls.owner) subquery2
from all_tables tbls;

SUBQUERY yang diletakkan pada SELECT clause harus yang bersifat single value. Karena itu Aggregate function seperti SUM, COUNT, MIN, MAX biasanya digunakan pada SUBQUERY jenis ini.

Pages: [1]   Go Up
Jump to:  

Powered by SMF 1.1.8 | SMF © 2006-2008, Simple Machines LLC
Theme updated by Runic Warrior.  Originally Designed by m3talc0re.
Page created in 0.024 seconds with 22 queries.