Fortis Central Forum
June 26, 2019, 02:53:46 PM *
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
  Print  
Author Topic: Oracle/PLSQL: Subqueries  (Read 3717 times)
0 Members and 1 Guest are viewing this topic.
Seraphim
Executive Manager
***

Karma: +0/-0
Offline Offline

Posts: 268


Owen Ario Hendrawan, S.KOM


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

Apa itu SUBQUERY?

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

Contoh:

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.

Contoh:

select suppliers.name, 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

Contoh:

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.
Logged

Pages: [1]   Go Up
  Print  
 
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.