Oracle SQL Tutorial -13 Data Processing With Sub-Questions

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.