SQL> SELECT REGION_NAME FROM REGIONS
2 WHERE REGION_ID =
3 (SELECT REGION_ID FROM COUNTRIES
4 WHERE COUNTRY_NAME ='Canada');
REGION_NAME
-------------------------
Americas
SQL> SELECT REGION_NAME FROM REGIONS
2 WHERE REGION_ID =
3 (SELECT REGION_ID FROM COUNTRIES );
(SELECT REGION_ID FROM COUNTRIES )
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
SQL> SELECT REGION_NAME FROM REGIONS
2 WHERE REGION_ID in
3 (SELECT REGION_ID FROM COUNTRIES) ;
REGION_NAME
-------------------------
Europe
Americas
Asia
Middle East and Africa
SELECT column, column, … FROM table
WHERE (column, column,…) IN
(SELECT column,column… FROM table
WHERE condition) ;
SQL> SELECT FIRST_NAME,
2 LAST_NAME,
3 JOB_ID
4 FROM EMPLOYEES
5 WHERE (EMPLOYEE_ID,DEPARTMENT_ID) IN
6 (SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM job_history );
FIRST_NAME LAST_NAME JOB_ID
-------------------- ------------------------- ----------
Payam Kaufling ST_MAN
Jonathon Taylor SA_REP
Michael Hartstein MK_MAN
SQL> SELECT employee_id,
2 manager_id,
3 department_id
4 FROM employees
5 WHERE (manager_id,department_id) IN
6 (SELECT manager_id,department_id FROM employees WHERE employee_id = 107
7 ) ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
104 103 60
105 103 60
106 103 60
107 103 60
SQL> SELECT employee_id,
2 manager_id,
3 department_id
4 FROM employees
5 WHERE manager_id IN
6 (SELECT manager_id FROM employees WHERE employee_id = 107
7 )
8 AND department_id IN
9 (SELECT department_id FROM employees WHERE employee_id = 107
10 ) ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
104 103 60
105 103 60
106 103 60
107 103 60
SQL> SELECT DEPARTMENT_NAME FROM DEPARTMENTS
2 WHERE department_id IN (10,20,30,40);
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources
SQL> select employee_id,
2 first_name,
3 last_name,
4 (
5 case
6 when department_id =
7 (SELECT department_id FROM departments WHERE location_id=1800
8 )
9 then 'Toronto'
10 else 'others'
11 END) location FROM employees ;
EMPLOYEE_ID FIRST_NAME LAST_NAME LOCATIO
----------- -------------------- ------------------------- -------
100 Steven King others
101 Neena Kochhar others
102 Lex De Haan others
103 Alexander Hunold others
104 Bruce Ernst others
105 David Austin others
106 Valli Pataballa others
107 Diana Lorentz others
SQL> SELECT DEPARTMENT_NAME
2 FROM DEPARTMENTS DEPT
3 WHERE EXISTS
4 (SELECT department_id FROM EMPLOYEES WHERE department_id =dept.department_id );
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
11 rows selected.
UPDATE table1 alias1
SET column = ( SELECT expression FROM table2 alias2
WHERE alias1.column = alias2.column ) ;
Example:
UPDATE employee e SET department_name = (select department_name
FROM departments d WHERE e.department_id = d. department_id);
Similarly Correlated Delete can be used
SQL> SELECT e.first_name
2 FROM employees e
3 WHERE e.employee_id NOT IN
4 (SELECT m.manager_id FROM employees m
5 );
-----------------------------
no rows selected
SQL> SELECT e.first_name,e.last_name FROM employees e
2 WHERE e.employee_id NOT IN (SELECT NVL(m.manager_id,0)
3 FROM employees m );
FIRST_NAME LAST_NAME
-------------------- -------------------------
Clara Vishney
Jason Mallin
Hazel Philtanker
Nanette Cambrault
Alana Walsh
Bruce Ernst
Nandita Sarchand
Elizabeth Bates
Kevin Feeney
Peter Tucker
Curtis Davies
FIRST_NAME LAST_NAME
-------------------- -------------------------
Randall Matos
Randall Perkins
Karen Colmenares
…… ……..
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Great article. Voted "Yes".
VB