• 1,Subquery
  • 1.1,Introduction to Subqueries
  • 1.2,WITH Subquery
• 2,Aggregate query
  • 2.1,UNION and UNION ALL
  • 2.2,MINUS
  • 2.3,INTERSECT
  • 2.4,Set Operations and ORDER BY
• 3,DISTINCT clause
  • 3.1,General usage
  • 3.2,Parameters for aggregation functions
• 4,summary

1. Subquery

1.1. Introduction to Subqueries

A sub-query is a query nested in SELECT, INSERT, UPDATE or DELETE statements or other sub-queries. Subqueries can be used wherever expressions are allowed. In other words, subqueries can appear almost anywhere in an SQL statement, and the definition of subqueries must be wrapped in a pair of parentheses. Section 4.2 of Article 12 of this series of blogs Three common sub-query writing methods have been demonstrated. Subqueries are also called internal queries or internal selections, and statements containing subqueries are also called external queries or external selections.

I have consulted a lot of data, it seems that there is no "correct sub-query classification method". Common classification names are: single-row sub-query, multi-row sub-query, multi-column sub-query, related sub-query, scalar quantum query, inline view, etc. The first three as the name implies, the last three are briefly described as follows:

• Relevant subqueries: Subqueries refer to one or more columns contained in external queries, that is to say, internal queries rely on external queries to obtain values. Such subqueries are called related subqueries, also known as repetitive subqueries.
• Scalar quantum query: Subqueries that return only a single value are called scalar quantum queries.
• Inline View: Generally refers to the sub-query written after the FROM clause. It is essentially a view, also known as embedded view. The difference between inline view and standard view is that there is no need to write the statement to create the view in advance to facilitate the execution of the query.

Let me add a few more examples of WHERE clause band queries:

-- Query the names and birthdays of low-income employees
SELECT t.staff_name,t.birthday FROM demo.t_staff t 
WHERE EXISTS(SELECT 1 FROM demo.t_staff_low n WHERE n.staff_id=t.staff_id);

-- Search for the name and birthday of the employee with the highest salary
SELECT t.staff_name,t.birthday FROM demo.t_staff t 
WHERE t.post_salary = (SELECT MAX(n.post_salary) FROM demo.t_staff n);

-- Search for the name and date of the employee whose post salary is higher than the average salary of any department
SELECT t.staff_name,t.birthday FROM demo.t_staff t 
WHERE t.post_salary > ALL(SELECT AVG(n.post_salary) FROM demo.t_staff n GROUP BY n.dept_code);

-- Look up the names and birthdays of all the girls in the test department (no one usually writes that way, just for demonstration)
SELECT t.staff_name,t.birthday FROM demo.t_staff t 
WHERE (t.dept_code,t.gender) = (SELECT '010104',0 FROM DUAL);

-- Query the names and birthdays of girls in any low-income sector
SELECT t.staff_name,t.birthday FROM demo.t_staff t 
WHERE (t.dept_code,t.gender) = ANY(SELECT n.dept_code,0 FROM demo.t_staff_low n);

-- The number of departments whose average post salary is higher than the average post salary of the company and the average post salary of the inquiry Department
SELECT t.dept_code,COUNT(1) count_staff,AVG(t.post_salary) avg_salary 
FROM demo.t_staff t 
GROUP BY t.dept_code 
HAVING AVG(t.post_salary)>(SELECT AVG(n.post_salary) FROM demo.t_staff n) 
ORDER BY t.dept_code;

1.2. WITH Subquery

The function of WITH sub-queries is similar to inline views, and other sub-queries, including inline views, can only be referenced once; while WITH sub-queries need to be defined before reference, and can be repeated by name once defined in the subsequent part of the whole query, which is very similar to temporary tables. Oracle has supported recursive WITH since 11g R2, that is to say, allowing itself to be referenced in the definition of WITH sub-queries, while other databases, such as (MS)SQL Server, PostgreSQL, DB2, etc., all support this feature prior to Oracle. Syntax example:

WITH query_name AS(SELECT ...)
SELECT ...

Case 1: Query the basic information of employees aged 25 years and under with fixed salary of 5000 or above. Example:

WITH t AS(
  SELECT t1.staff_name,t2.enum_name dept_name,DECODE(t1.gender,1,'male',0,'female','Unknown') gender,
    EXTRACT(YEAR FROM fn_now)-EXTRACT(YEAR FROM t1.birthday) age,base_salary+post_salary fixed_salary
  FROM demo.t_staff t1
  LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT'
  WHERE t1.is_disabled=0
) 
SELECT t.dept_name,t.staff_name,t.gender,t.age,t.fixed_salary 
FROM t WHERE t.age<=25 AND t.fixed_salary>=5000

Result:

DEPT_NAME                                          STAFF_NAME                                         GENDER        AGE FIXED_SALARY
-------------------------------------------------- -------------------------------------------------- ------ ---------- ------------
Developing a Big Male 25 6500
 Research and Development of Three Xiaoling Women 23 5400
 Research and Development of Three Han Sanmen 24 7550
 Testing Department Xiaoyannu 25 5600

Case 2: Statistics of the number of departments, total wages, average wages, maximum wages, minimum wages. Example:

WITH
t1 AS (SELECT n.dept_code FROM demo.t_staff n GROUP BY n.dept_code),
t2 AS (SELECT n.enum_code dept_code,n.enum_name dept_name FROM demo.t_field_enum n WHERE n.field_code='DEPT'),
t3 AS (SELECT t.dept_code,COUNT(1) count_staff FROM demo.t_staff_salary t GROUP BY t.dept_code),
t4 AS (SELECT t.dept_code,SUM(t.fixed_salary) sum_salary FROM demo.t_staff_salary t GROUP BY t.dept_code),
t5 AS (SELECT t.dept_code,AVG(t.fixed_salary) avg_salary FROM demo.t_staff_salary t GROUP BY t.dept_code),
t6 AS (SELECT t.dept_code,MAX(t.fixed_salary) max_salary FROM demo.t_staff_salary t GROUP BY t.dept_code),
t7 AS (SELECT t.dept_code,MIN(t.fixed_salary) min_salary FROM demo.t_staff_salary t GROUP BY t.dept_code)
SELECT t2.dept_name,t3.count_staff,t4.sum_salary,t5.avg_salary,t6.max_salary,t7.min_salary
FROM t1,t2,t3,t4,t5,t6,t7
WHERE t1.dept_code=t2.dept_code AND t1.dept_code=t3.dept_code AND t1.dept_code=t4.dept_code 
AND t1.dept_code=t5.dept_code AND t1.dept_code=t6.dept_code AND t1.dept_code=t7.dept_code
ORDER BY t1.dept_code;

Result:

DEPT_NAME                                          COUNT_STAFF SUM_SALARY AVG_SALARY MAX_SALARY MIN_SALARY
-------------------------------------------------- ----------- ---------- ---------- ---------- ----------
R&D of a 4 36002 9000.5 10501 6500
 R&D II 2 18500 9250 10000 8500
 R&D 3 2 12950 6475 7550 5400
 Testing Unit 2 12700 6350 7100 5600
 Implementing a 3 18700 6233.33333 8500 5100
 Implementation 2 15700 7850 8000 7700

Case 2 An example of not using WITH:

SELECT t2.enum_name dept_name,t3.count_staff,t4.sum_salary,t5.avg_salary,t6.max_salary,t7.min_salary
FROM(SELECT n.dept_code FROM demo.t_staff n GROUP BY n.dept_code) t1
LEFT JOIN demo.t_field_enum t2 ON t1.dept_code=t2.enum_code AND t2.field_code='DEPT'
LEFT JOIN(SELECT t.dept_code,COUNT(1) count_staff FROM demo.t_staff_salary t GROUP BY t.dept_code) t3 
  ON t1.dept_code=t3.dept_code
LEFT JOIN(SELECT t.dept_code,SUM(t.fixed_salary) sum_salary FROM demo.t_staff_salary t GROUP BY t.dept_code) t4 
  ON t1.dept_code=t4.dept_code
LEFT JOIN(SELECT t.dept_code,AVG(t.fixed_salary) avg_salary FROM demo.t_staff_salary t GROUP BY t.dept_code) t5 
  ON t1.dept_code=t5.dept_code
LEFT JOIN(SELECT t.dept_code,MAX(t.fixed_salary) max_salary FROM demo.t_staff_salary t GROUP BY t.dept_code) t6 
  ON t1.dept_code=t6.dept_code
LEFT JOIN(SELECT t.dept_code,MIN(t.fixed_salary) min_salary FROM demo.t_staff_salary t GROUP BY t.dept_code) t7 
  ON t1.dept_code=t7.dept_code
ORDER BY t1.dept_code;

2. Collection Query

In mathematics, we can do intersection and difference operations on sets, and in SQL, we can also do intersection and difference operations on query result sets. These three kinds of SQL set queries correspond to operator keywords: INTERSECT, UNION/UNION ALL, MINUS.

2.1, UNION and UNION ALL

The most common SQL set query is union query, and the union set query operators are UNION and UNION ALL, which have the same usage. Example:

-- Search for Bachelor's Degree Girls and Post-1993 Employees
SELECT t1.staff_name,t1.gender,t1.edu_bg,t1.birthday FROM demo.t_staff t1 WHERE t1.gender=0 AND t1.edu_bg=1
UNION ALL
SELECT t2.staff_name,t2.gender,t2.edu_bg,t2.birthday FROM demo.t_staff t2 WHERE t2.birthday>=TO_DATE('1993-01-01','yyyy-mm-dd');

Result:

STAFF_NAME                                         GENDER EDU_BG BIRTHDAY
-------------------------------------------------- ------ ------ -----------
Xiaoling 0 1 1994-06-17
 Han San11 1993-08-18
 Xiaoling 0 1 1994-06-17

From the results of UNION and UNION ALL queries, one of their differences is that UNION will remove duplicate rows, while UNION ALL will retain duplicate rows. If the ALL keyword in the above example is removed, one row in line 1 and 3 will be removed as duplicate rows. The other difference is that the default ranking of result sets is different, UNION ALL organizes data in association order only, not in association order. Sort again, and UNION will sort the entire data set according to the default rules. In addition, UNION ALL is more efficient than UNION, so UNION ALL is relatively common.

2.2,MINUS

The difference set between the two result sets can be obtained by the MINUS operator. The difference set result set does not include duplicate rows and is sorted by default rules. Example:

-- query demo.t_staff There are demo.t_staff_copy Employees not on the table
SELECT t1.staff_name,t1.gender,t1.birthday FROM demo.t_staff t1
MINUS
SELECT t2.staff_name,t2.gender,t2.birthday FROM demo.t_staff_copy t2;

Result:

STAFF_NAME                                         GENDER BIRTHDAY
-------------------------------------------------- ------ -----------
Great Powers 1 1992-01-15
 Li Yang 1 1989-01-14
 Xulai 1 1991-04-01

2.3,INTERSECT

The intersection of two result sets can be obtained through the INTERSECT operator. The intersection result set does not include duplicate rows and is sorted by default rules. Example:

-- Inquire about low-income young people
SELECT t1.staff_name,t1.gender,t1.dept_code FROM demo.t_staff_young t1
INTERSECT
SELECT t2.staff_name,t2.gender,t2.dept_code FROM demo.t_staff_low t2;

Result:

STAFF_NAME                                         GENDER DEPT_CODE
-------------------------------------------------- ------ --------------------
Wang Er 1 010101
 Xiaohong 0 010201

2.4. Set Operations and ORDER BY

Regardless of the set query in the intersection difference, only the ORDER BY clause is added after the last query to sort the whole result set. Example:

SELECT t1.staff_name,t1.gender,t1.dept_code FROM demo.t_staff t1 WHERE t1.gender=0 AND ROWNUM <= 1
UNION ALL
SELECT t2.staff_name,t2.gender,t2.dept_code FROM demo.t_staff_young t2 WHERE ROWNUM <= 1
UNION ALL
SELECT t3.staff_name,t3.gender,t3.dept_code FROM demo.t_staff_low t3 WHERE t3.gender=0
ORDER BY dept_code;

Result:
STAFF_NAME GENDER DEPT_CODE
-------------------------------------------------- ------ --------------------------------------------------
Xiaoqiang 1 010101
Xiaoling 0 010103
Xiaohong 0 010201
One detail to note is that the sorting field after ORDER BY cannot be qualified by any aliases, which is understandable. After all, the sorting operation is for the entire result set. That is to say, dept_code after ORDER BY in the example above does not belong to any table in t1, t2, t3, but to the whole result set. If the field names in tables t1, T2 and T3 are different, how can the sorted field names be determined? My test result is that it must be the field name or column alias in the first query, but only if the column in all subsequent queries is the real field name, or the column alias is the same as the field name or column alias in the first query. If you don't think this rule is too complicated to remember, you can also give the column of all queries the same alias. Another grammatical rule of ORDER BY can also be used: ORDER BY N, which is to write the column number of the sorting field directly. If the query above wants to be sorted by column 2, write this as follows:

SELECT t1.staff_name,t1.gender,t1.dept_code FROM demo.t_staff t1 WHERE t1.gender=0 AND ROWNUM <= 1
UNION ALL
SELECT t2.staff_name,t2.gender,t2.dept_code FROM demo.t_staff_young t2 WHERE ROWNUM <= 1
UNION ALL
SELECT t3.staff_name,t3.gender,t3.dept_code FROM demo.t_staff_low t3 WHERE t3.gender=0
ORDER BY 2;

Result:

STAFF_NAME                                         GENDER DEPT_CODE
-------------------------------------------------- ------ --------------------------------------------------
Xiaoling 0 010103
 Xiaohong 0 010201
 Xiaoqiang 1 010101

3. DISTINCT clause

The function of the DISTINCT clause is to remove duplicate rows from a set of data and leave the only data.

3.1. Common usage

Example:

SELECT DISTINCT t.dept_code FROM demo.t_staff t; -- Query all departments with the same effect
SELECT t.dept_code FROM demo.t_staff t GROUP BY t.dept_code;

3.2. Parameters of Aggregation Function

DISTINCT is supported in COUNT, SUM, MAX, MIN and AVG.
Syntax:

SELECT func(DISTINCT field) FROM table [WHERE conditions];

Example 1:

-- Statistics on the number of departments with female employees
SELECT COUNT(DISTINCT t.dept_code) count_dept FROM demo.t_staff t WHERE t.gender=0;

Example two:

-- The average wages of all employees are counted. If there are many employees with the same wages, the wages of only one of them are counted.
SELECT AVG(DISTINCT t.fixed_salary) avg_salary FROM demo.t_staff_salary t;

4, summary

This paper mainly describes the concept of Oracle sub-query, WITH sub-query, set query and set de-duplicate statement DISTINCT. Among them, sub-query is the most important. No matter what kind of relational database you use, you must master it skillfully. And the WITH sub-query and DISTINCT clause and set query are also more practical SQL functions, it is best to master.