Hi ,
In this article I will tell you about multi-column subqueries and related subqueries .Subqueries are used to join data , and the value obtained with the subquery can be used in the parent query .Nested queries consist of a child and a parent query .
MULTIPLE COLUMN SUB-QUERY – DOUBLE WAY
-
To list contacts with the same job_id and the same employee_id as the Melike contact in the ADMIN.NEW_TABLE table :
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE (EMPLOYEE_ID,JOB_ID) IN (SELECT EMPLOYEE_ID,JOB_ID FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='Melike');
MULTIPLE COLUMN SUB-QUERY – ONE WAY
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='Melike') AND JOB_ID IN (SELECT JOB_ID FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='Melike' )AND FIRST_NAME <> 'Melike';
SCALAR SUB QUESTIONS
-
In the Scalar query type , subqueries return a column and a row , and return values are usually numeric .
Where to use ?
-
In conditional sentences such as Decode and Case ,
-
In Group by statements ,
-
They are used in the Where , Set , and Update sentences .
Related Subqueries
Associated subqueries run line by line , and subqueries are written separately for each row in the external query . There are 3 kinds of associated subqueries :
-
GET : The corresponding row in the external query is retrieved .
-
EXECUTE : The internal query runs using the value of the corresponding row in the external query .
-
USE : Based on the value from the internal query, it is decided whether to retrieve the row in the external query .
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY FROM ADMIN.NEW_TABLE e WHERE SALARY>(SELECT AVG(SALARY) FROM ADMIN.NEW_TABLE WHERE JOB_ID=e.JOB.ID);
EXISTS
- Tests whether rows are in a subquery . If the appropriate record is found in the row in the subquery , the result is TRUE and the search ends in the subquery . If the appropriate record is not found in the row in the subquery , the result is FALSE and the search continues in the subquery .
-
Now in our example we have two paintings called students and departments . If the student has a section_id value in the section table , the list of students is listed .
SELECT OGRENCI_NO,AD,SOYAD FROM OGRENCI WHERE EXISTS(SELECT * FROM BOLUMLER WHERE OGRENCI.BOLUM_ID=BOLUMLER.BOLUM_ID);
NOT EXISTS
-
Now we will do the opposite of the above example .
SELECT OGRENCI_NO,AD,SOYAD FROM OGRENCI WHERE NOT EXISTS(SELECT * FROM BOLUMLER WHERE OGRENCI.BOLUM_ID=BOLUMLER.BOLUM_ID);
RELATED UPDATE
ALTER TABLE OGRENCI ADD(BOLUM_ADI VARCHAR2(30));
UPDATE OGRENCI a SET BOLUM_ADI=(SELECT BOLUM_ADI FROM BOLUMLER b WHERE a.BOLUM_ID=b.BOLUM_ID);
SELECT * FROM OGRENCI;
WITH
-
With a statement , multiple repetitions in a complex query allow us to construct the same query block in a Select statement .
-
The results of the query block are stored in the temporary table field .
-
Increases performance .
WITH QUERY_NAME AS ( SQL QUERY ) SELECT * FROM QUERY_NAME;
See you in my next post.