ÈÓã Çááå ÇáÑÍãä ÇáÑÍíã
ÇáÏÑÓ ÇáÓÇÈÚ


äÓÊÃäÝ Çáíæã ãÇ ÈÏÃäÇ Èå ÓÇÈÞÇ æäßãá ßíÝíÉ ÇáÊÚÇãá ãÚ ÌãáÉ Select stament
ÝßãÇ ÞáÊ åí ÇáÇÓÇÓ Ýí ÇÓÊÚÑÇÖ ÇáÈíÇäÇÊ æÇÓÊÛáÇá ÇáÝÇäßÔä ÇáÊí Êã ÔÑÍåÇ ÈÇáÓÇÈÞ ÊÚØí ÞÕÑ ááæÞÊ æÚÑÖ ÕÍíÍ ááÈíÇä ÇáÕÍíÍ

ÓæÝ äÓÊÚÑÖ Çáíæã Çá Subqueries Çæ ãÇ íÓãì ÈÌãá Çá selectÇáãÊÏÇÎáÉ æåí ÊÃÊí ÈÃÔßÇá ãÊÚÏÏÉ äÐßÑ ÇÔåÑåÇ æãÇ íãßä ÇáÊÚÇãá ãÚåÇ

ÇÔßÇá Çá Subqueries

1- WHERE CONDITION

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');

áÇÍÙæÇ Çä ãÍæÑ ÇáÍÏË ßáå Íæá WHERE æÇÓÊÎÏãäÇ Çá IN áÊæÖÍ ãÝåæã åá åÐÇ ÇáÈíÇä ãæÌæÏ Öãä ãÌãæÚÉ ãä ÇáÈíÇäÇÊ Ýí ÌÏæá ÂÎÑ
æßÃääÇ äÞæá ÇÚÑÖ áäÇ ÌãíÚ ÇáÈíÇäÇÊ ãä ÇáÌÏæá all_tables Úáì Çä íßæä ãËáÇ ÇÓã ÇáÌÏæá ãæÌæÏ Öãä ÌãáÉ select ÇÎÑì ÈÍíË ÊÑÌÚ ÌãíÚ ÇÓãÇÁ ÇáÌÏÇæá æÇÎÕ åäÇ æÇÑßÒ Çä ÇÓã ÇáÍÞá ÞÈá IN íÌÈ Çä íßæä äæÚ ÇáÈíÇäÇÊ Þí ÌãáÉ Çá select ÈÚÏ IN ãÊæÇÝÞÉ ÈÍíË áÇíÌæÇä Çä ÊÞæá

where cols.column_name in (select * from table_name)

æíæÌÏ äæÚíä ÂÎÑíä ÓæÝ íÊã ÊæÖÍåãÇ ÝíãÇ ÈÚÏ Çä ÔÇÁ Çááå
æåÐÇ ãËÇá ÊæÖÍíÍ Úáì ãÇ ÓÈÞ
ÇÚÑÖ ÇÓã ÇáãæÙÝ æÑÞãå Úáì Çä íßæä ÇáãæÙÝíä Ýí ÇáÇÏÑÇÉ ÇáÚÇãÉ

SELECT EMP_NAME,EMP_NO FROM EMPLOYEES
WHERE DPT_NO IN (SELECT DPT_NO FROM DEPARTMENTS
WHERE DPT_NAME='ÇáÇÏÑÇÑÉ ÇáÚÇãÉ'

åäÇ ÓæÝ íÚÑÖ ÇÓãÇÁ ÇáãæÙÝíä ÇáÐíä íäÊãæä Çáì ÇáÇÏÇÑÉ ÑÞã 10 ãËáÇ Úáì Çä Êßæä åÐå ÇáÇÏÇÑÉ Öãä ÌãáÉ Çá SELECT ÇáËÇäíÉ æÊßæä ÇÓãåÇ ÇáÇÏÇÑÉ ÇáÚÇãÉ

* UNION Query
æíÞÕÏ ÈåÇ ÏãÌ ÇÓÊÚáÇãíä áÍÞáíä ãÊÔÇÈåíä Çæ ÇßËÑ Ýí ÌÏæáíä ãÎÊáÝíä
æíÓÊÝÇÏ ãäåÇ ÈÚÏã ÇÙåÇÑ ÇáÈíÇäÇÊ ÇáãÊßÑÑÉ Úáì Çä íßæä ÚÏÏ ÇáÍÞæá Ýí ÇáÌÏæá ÇáÇæá ãÓÇæí áÚÏÏ ÇáÍÞæá ááÌÏæá ÇáËÇäí

select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;

ãËÇá

select supplier_id
from suppliers
UNION
select supplier_id
from orders;

ÇÓÊÎÏÇã With ORDER BY Clause ãÚ union
ÊæÖÚ Ýí ÇÎÑ Çá unio æããä Çä ÊÑãÒ áÇÓã ÇáÍÞá ÇáÇæá ÈÑÞã 1æåßÐÇ

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

ÑÞã 2 åæ ÑãÒ supplier_name æßÐáß company_name æáÇíÚäí ÇáÑÞã ÚÏÏ ÇáÍÞæá æáßä ÊÚäí ÇáÊÑÊíÈ Ýáæ æÍÏäÇ ÇáÇÓãÇÁ áßÊÈäÇ ÇáÇÓãÇÁ ÈÚÏ Order By
CODE]select supplier_id, supplier_name as "name"
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name as "name"
from companies
where company_id > 1000
ORDER BY name;[/code]

ÇÓÊÎÏÇã UNION ALL Query
åí äÝÓ ãÈÏà Úãá Çá union æáßä ÊÚÑÖ ÌãíÚ ÇáÈíÇäÇÊ Ýí ÇáÌÏæáíä ãÚ ÇáÊßÑÇÑ

select field1, field2, . field_n
from tables
UNION ALL
select field1, field2, . field_n
from tables;

ãËÇá

select supplier_id
from suppliers
UNION ALL
select supplier_id
from orders;

INTERSECT Query

æãÈÏà Úãáå íÔÈÉ Çá union æåæ ÏãÌ ÇáÈíÇäÇÊ ãÚ ÝÇÑÞ Çäå íÚÑÖ ÇáÈíÇäÇÊ ÇáãæÌæÏÉ Ýí ÇáÌÏæá ÇáÇæá æÇáÊí ÛíÑ ãæÌæÏÉ Ýí ÇáÌÏæá ÇáËÇäí íÚäí íÚÑÖ ÇáÛíÑ ãßÑÑ ÝÞØ
select field1, field2, . field_n

from tables
INTERSECT
select field1, field2, . field_n
from tables;

ãËÇá

select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;

MINUS Query

åí ÚãáíÉ ÇÑÌÇÚ ÇáÈíÇäÇÊ Ýí ÇáÇÓÊÚáÇã ÇáÇæá æÇáÛíÑ ãæÌæÏ Ýí ÇáÇÓÊÚáÇã ÇáËÇäí

select field1, field2, . field_n
from tables
MINUS
select field1, field2, . field_n
from tables;

ãËÇá

select supplier_id
from suppliers
MINUS
select supplier_id
from orders;

æíÌÈ Çä Êßæä ÚÏÏ ÇáÍÞæá ãÊÔÇÈåå æÇáäæÚ ßÐáß
æãÚäì ÇáãËÇá ÇáÓÇÈÞ ÇÚÑÖ ÇáÈíÇäÇÊ Ýí ÌãáÉ ÇáÇÓÊÚáÇã ÇáÇæáì æÇáÛíÑ ãæÌæÏÉ ÈÇáÇÓÊÚáÇã ÇáÊÇáí
ßÃä ÊÞæá äÑíÏ Çä äÚÑÖ ÇÓãÇÁ ÇáãæÑÏíä ÇáÐíä áã íÊã ÇáØáÈ Úáì ãäÊÌÇÊåã

ÇáÃÓÆáÉ

1-Èíä ÇÓÊÎÏÇã Union Ýí ÌãáÉ Select
2-Èíä ÇÓÊÎÏÇã Çá count Ýí union
3-ãÇ ÇáÝÑÞ Èíä INTERSECT Query æ Minus Query

ÇäÊåÊ ÇáÇÓÆáÉ

ÇäÊåì ÇáÏÑÓ ÇáÓÇÈÚ