Oracle Database 11g – SQL Temelleri II ||Subqueries

Diğer dersleri http://selimkaratas.com.tr/oracle-11g-r2-egitimi-ders-notlari adresinden bulabilirsiniz.

6. Retrieving Data Using Subqueries ( Subqueries Kullanarak Veri Alma)

Kolon Karşılaştırma

•  Nonpairwise comparisons
•  Pairwise comparisons

Pairwise Comparison Subquery

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE first_name = 'Alexander')
AND first_name <> 'Alexander';

-- alexander ile aynı menejere bağlı kişilerin employee_id, manager_id, department_id bilgilerini aldık. ( alexander hariç)

Nonpairwise Comparison Subquery

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN

(SELECT manager_id
FROM employees
WHERE first_name = 'Alexander')

AND department_id IN

(SELECT department_id
FROM employees
WHERE first_name = 'Alexander')
AND first_name <> 'Alexander';

-- İki tane subquery yaparak yine aynı sonuca ulaşırız.

Scalar Subquery Expressions

CASE expressions:

SELECT employee_id, last_name,
(CASE
WHEN department_id =
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

-- location_id=1800 olanların departmant_id'lerini al doğru ise Canada değilse USA yaz.

ORDER BY clause:

SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

 

 

Using Correlated Subqueries