HOW TO WRITE A MULTI-COLUMN SUBQUERY?
ANSWER: A MULTI-COLUMN SUBQUERY
IS SIMPLY A SUBQUERY THAT RETURNS MORE THAN ONE COLUMN. HERE IS A WORKING
EXAMPLE OF A MULTI-COLUMN SUBQUERY:
SELECT * FROM HR.EMPLOYEES
WHERE ( FIRST_NAME , LAST_NAME) IN ( SELECT FIRST_NAME , LAST_NAME FROM HR.EMPLOYEES
WHERE FIRST_NAME LIKE 'S%'
AND LAST_NAME LIKE 'K%'
)
/*OUTPUT*/
|
EMPLOYEE_ID
|
FIRST_NAME
|
LAST_NAME
|
EMAIL
|
JOB_ID
|
SALARY
|
MANAGER_ID
|
DEPARTMENT_ID
|
|
100
|
Steven
|
King
|
SKING
|
AD_PRES
|
24,000
|
|
90
|
|
173
|
Sundita
|
Kumar
|
SKUMAR
|
SA_REP
|
6,100
|
148
|
80
|
Is it mandatory to have tow column in sub-query?
ReplyDeleteNo. of column before "IN" clause and No. of column after "IN" clause should match
ReplyDeleteQuery can also be rewritten as below which yields the same result too.
E.g.
SELECT * FROM HR.EMPLOYEES
WHERE FIRST_NAME IN ( SELECT FIRST_NAME FROM HR.EMPLOYEES
WHERE FIRST_NAME LIKE 'S%' )
and LAST_NAME) IN ( SELECT LAST_NAME FROM HR.EMPLOYEES
WHERE LAST_NAME LIKE 'K%' )