Data Processing With Sub-Questions

Hi ,

In this article I will tell you about multi-column subqueries and related subqueries .

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 :

MULTIPLE COLUMN SUB-QUERY – ONE WAY

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 .

EXISTS

  • Tests whether rows are in a subquery . If the appropriate record is found in the subquery , the result is TRUE , and the search ends in the subquery if the appropriate record is not found 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 .

NOT EXISTS

  • Now we will do the opposite of the above example .

RELATED UPDATE

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.