SQL nested subqueries in STRUCTURED Query Language (with Mysql as an example)

@[TOC]

Query the database file downloads used

SQL provides a nested subquery mechanism: a subquery is a select-from-WHERE expression nested within another query. Subqueries are nested in where clauses and are usually used to check the membership, comparison, and cardinality of collections. Subqueries can also be nested in the FROM clause; In addition, there is another kind of subquery is the standard quantum query.

1. Collection membership

SQL allows testing the membership of a meta-ancestor in a relationship:
- The connecter in tests whether a meta-ancestor is a member of a set;
- The connecter not in tests whether a meta-ancestor is not a member of a set;

Example:

Find all the courses that started at the same time in fall 2009 and spring 2010.

select distinct course_id
from section
where semester='Fall' and year=2009 and
	course_id in (select course_id
						 from section
			  			 where semeter='Spring' and year=2010);
Copy the code

Find all classes that start in fall 2009 but not in spring 2010 (difference arithmetic)

select distinct course_id
from section
where semester='Fall' and year=2009 and
course_id not in (select course_id
				  	  		from section
			  	     		where semeter='Spring' and year=2010)
Copy the code

Find the total number of students who took the segment taught by the teacher with ID 10101:

  select count(distinct ID)
  from takes
  where (course_id,sec_id,semester,year)
  in 
  (select course_id,sec_id,semester,year
  from teaches
  where teaches.ID = 10101) ` ` `![Insert picture description here](HTTPS://img-blog.csdnimg.cn/20210409222802793.png? x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTkyNTU5,size_16,color_FFFFFF,t_70 )Copy the code

In and not in can also be used with enumerated sets:
- Find out the names of the teachers named neither “Mozart” nor “Einstein”;
```
select distinct name 
from instructor 
where name not in ('Mozart'.'Einstein')
Copy the code
```

2. Comparison of sets

>some, <some, <=some, >=some, =some(equivalent to in), <>(! = some(not equivalent to not in)
>all, <all, <=all, >=all, =all(not equivalent to in), <>(! =)all(= not in)

Example:

Find the names of all teachers who earn at least more than a teacher in the Biology department (renaming is also acceptable)

select name
from instructor
where salary>some(select salary
				  from instructor
				  where dept_name = 'Biology');
Copy the code

Find the names of all the teachers who earn more than any other teacher in the Biology department

select name
from instructor
where salary>all(select salary
				  from instructor
				  where dept_name = 'Biology');
Copy the code

Find the departments with the highest average salaries

select dept_name
from instructor
group by dept_name
having avg(salary)> =all(select avg(salary)
				  	   from instructor
				  	   group by dept_name);
Copy the code

3. Exist, not exist

Exists returns true if the subquery that is an argument is non-null:
- Example: Find all the courses offered in the fall 2009 and spring 2010 semesters
```
select course_id
from section as S
where semester = "Fall" and year=2009 and
exists(
	select * 
	from section as T
	where semester = 'Spring' and year=2010 
	and S.course_id = T.course_id
)
Copy the code
```
- A correlated name from an outer query can be used in a SUB-query of a WHERE clause. Sub-queries that use correlated names from outer queries are called correlated Sub_queries. The query conditions of correlated subqueries depend on the parent query.
  - The process of understanding related subqueries:
    - Select the first meta-parent from the outer query table and process the inner query according to its attribute value related to the inner query. Suppose that the first meta-parent’s course ID is CS-111, we will query whether the cS-111 course will be opened in the spring of 2010. According to the first two conditions, that is, whether the course will be taught in the fall of 2009 to decide whether to add the final query results.
The not exists structure tests whether a meta-ancestor does not exist in the subquery result set, and returns true if it does not exist:
- If A contains B, not exists (B except A)
- Example: Identify students who have taken all the courses offered in the Biology department
```
selectS.I D, S.n amefrom student as S
where not exists((select course_id
	from course
	where dept_name = 'Biology')
	except
	(select course_id
	from takes as T
	where S.ID=T.ID
	)
)
Copy the code
```
  - select course_id from course where dept_name = 'Biology'All courses offered in the Biology department (B)select course_id from takes as T where S.ID=T.IDSelect * from Biology where (A) select * from Biology where (b) select * from Biology where (c) select * from Biology Otherwise, where is put back false.
  - Mysql has no except operands.
```
select S.ID,S.name
  from student as S
  where not exists(
  	select course_id
  	from course
  	where dept_name = 'Biology'
  	and course_id not in
  	(select course_id
  	from takes as T
  	where S.ID=T.ID
  	)
  );
Copy the code
```

4. Repeat the meta-ancestor existence test

Unique (computes the truth value on the empty set)\not unique tests whether the result of a subquery has a duplicate meta-ancestor

Example: Find all classes that were offered at most once in 2009

select T.course_id
from course as T
where unique (
			  select R.course_id
			  from section as R
			  where T.course_id = R.course_id and R.year = 2009
			  );
Copy the code

Mysql (5&8) does not have a unique subquery.

select T.course_id
from course as T
where 1> =(
	select count(R.course_id)
	from section as R
	where T.course_id = R.course_id and R.year = 2009
);
Copy the code

Example: Find all courses offered at least twice in 2009

select T.course_id
from course as T
where not unique (select R.course_id
			  from section as R
			  where T.course_id = R.course_id and R.year = 2009));Copy the code

Mysql still does not work.

select T.course_id
from course as T
where 2< =(
	select count(R.course_id)
	from section as R
	where T.course_id = R.course_id and R.year = 2009
);
Copy the code

5. Subquery in the from clause

Because any select-from-WHERE expression returns a relationship, it can be inserted into another select-from-WHERE expression where any relationship can occur;
Example 1: Find the average salary of the faculty in the department whose average salary exceeds $42,000
```
select dept_name,avg_salary
from((select dept_name,avg(salary) as avg_salary
	from instructor
	group by dept_name)
	as dept_avg
)
where avg_salary>42000;
Copy the code
```
- The query above uses the AS clause to name the result relationship of the subquery

Example 2: Find the total salary of the department with the largest total salary of all departments

select max(tot_salary)
from (
	select dept_name,sum(salary) tot_salary
	from instructor
	group by dept_name
) as dept_total;
Copy the code

6. With clause: Mysql 8 previously did not support it

The with clause provides a way to define temporary relationships that are valid only for queries that contain the with clause

Example:

Find the department with the largest budget :(the following query defines the temporary relationship max_budget)

with max_budget as 
(select max(budget) as value from department)
select budget
from department,max_budget
where department.budget=max_budget.value;
Copy the code

mysql 5

drop table if exists max_budget;
create temporary table max_budget(value double)
select max(budget) as value from department;
select budget
from department,max_budget
where department.budget=max_budget.value;
Copy the code

Find departments with a gross salary greater than the average for all departments:

with dept_total as(select dept_name,sum(salary) as value
from instructor
group by dept_name
),
dept_total_avg as(
select avg(value) as value
from dept_total
)
select dept_name
from dept_total,dept_total_avg
where dept_total.value> =dept_total_avg.value;
Copy the code

mysql 5

drop table if exists dept_total;
create temporary table dept_total(dept_name varchar(12), value double)
select dept_name,sum(salary) as value
from instructor
group by dept_name;
drop table if exists dept_total_avg;
create temporary table dept_total_avg(value double)
select avg(value) as value
from dept_total;
select dept_name
from dept_total,dept_total_avg
where dept_total.value> =dept_total_avg.value;
-- select * from dept_total;
-- select * from dept_total_avg;
Copy the code

7. Scalar quantum query

SQL allows sub-queries that return only a single meta-ancestor containing a single attribute to appear anywhere an expression returning a single value can appear. Such sub-queries are called scalar sub_query.

Example: List all the departments and the number of teachers they have

select dept_name,
	(select count(*)
	from instructor
	where department.dept_name = instructor.dept_name)
	as num_instructors
from department;
Copy the code

References: [1] Abraham Silberschatz, Henry F Korth, S Sudarshan． Database System Concepts. New York: McGraw-Hill, 2010 Database System Concepts

SQL nested subqueries in STRUCTURED Query Language (with Mysql as an example)

1. Collection membership

2. Comparison of sets

3. Exist, not exist

4. Repeat the meta-ancestor existence test

5. Subquery in the from clause

6. With clause: Mysql 8 previously did not support it

7. Scalar quantum query

Related Posts

Kibana: Create a link drilldown-7.11 for table

Why does TCP require a three-way handshake

Schematic Record – Linux interrupt basics