SQL Learning Note 4--nested subqueries (top)

Nested subqueries

A subquery is a select-from-where expression that is nested within another query. When a subquery is nested in a WHERE clause, it is often used to check the membership of the collection, the collection's comparison, and the cardinality of the collection.

1. Set Membership

SQL allows you to test the membership of a tuple in a relationship. The join word in tests whether a tuple is a member of a collection, which consists of a set of values produced by a SELECT clause. The join word not in test tuples are not members of the collection.

Consider "finding all courses in the fall of 2009 and the spring semester of 2010." "By the previous knowledge, you can write the query by using two sets and operations." As follows:

(Select course_id

FROM clause

where semester= ' Fall ' and year=2009)

Intersect

(Select course_id

FROM clause

where semester= ' Spring ' and year=2010);

Consider now to find out all the courses that start in the fall of 2009 and see if they are also members of the course collection that starts in the spring of 2010. Obviously, this query is the same as the former result.

(select distinct course_id/* This query is not automatically duplicated, so you must use the distinct*/

FROM clause

where semester= ' Fall ' and year=2009) and

course_id in (select course_id

FROM clause

where semester= ' Spring ' and year=2010);

In and not in can also be used to enumerate collections. For example, the following query finds the name of a teacher who does not call a or B:

SELECT DISTINCT name

From instructor

Where name is not in (' A ', ' B ');

2. Comparison of sets

Consider the query "find out the names of all teachers who meet the conditions below, and their wages are at least higher than that of a biology teacher." "Before that, we'll do the following:

SELECT DISTINCT T.name

From instructor as t,instructor as S/* Renaming operations Common occasions */

where T.salary>s.salary and s.dept_name= ' biology ';

In SQL, the phrase "at least larger than one" is represented in SQL with >some, which allows us to use a query that is more literal:

Select Name

From instructor

where salary>some(Select Salary

From instructor

Where dept_name= ' biology ');

In addition, SQL also allows comparisons of <some, <=some, >=some,=some, and <>some. The =some is equivalent to in, however <>some is not equivalent to not in.

Change the conditions of the above query a little bit, and consider "finding out the names of all teachers who meet the conditions below, whose wages are at least higher than the salary of each teacher in the biology department." ”

The structure >all corresponds to the phrase "larger than all". The wording is as follows:

Select Name

From instructor

Where salary>All (Select Salary

From instructor

Where dept_name= ' biology ');

Similarly, SQL allows comparisons of <all,<=all,>=all,=all and <>all. The <>all is equivalent to not in but =all is not equivalent to in

3, empty relationship test: exists and NOT exists (* difficult)

SQL also has a feature that allows you to test whether a tuple exists in the results of a subquery. The EXISTS structure returns True when a subquery that is a parameter is not empty.

Use the exists structure to write a query "Find all courses in the fall semester of 2009 and the spring semester of 2010." ”

Select course_id

FROM clause as S

where semester = ' Fall ' and year=2009 and

Exists (SELECT *

FROM clause as T

where semester= ' Spring ' and year = and

S.COURSE_ID=T.COURSE_ID);

The above query illustrates an attribute of SQL, and a related name from the outer query (s in the query) can be used in the subquery of the WHERE clause. This subquery, which uses a correlation name from the outer layer, is called a correlated subquery.

Note: As long as there are exists related subqueries, EXISTS returns only a bool value and tests whether there are tuples in the subquery results.

The NOT EXISTS structure is used to test whether there are no tuples in the subquery results. You can use not exists to simulate the collection containment operation:

For example, relationship A contains relationship B that can be written as "not EXISTS (b except A)"

Consider the query "find students who have taken the biology department to open all courses". We can understand that some students take courses that include all the courses offered by the biology department. Use the not exists-except structure as follows:

Select S.id,s.name

From student as S

Where NOT EXISTS ((select course_id

From course

Where dept_name= ' biology ')/* subquery 1, find out the collection of all courses offered by the biology department */

Except

(Select S.id

From takes as T

where s.id=t.id)); /* Sub-query 2, find all s.id elective courses * *

In this example, the outer select tests each student's collection of all the courses they take to include all the course collections offered by the biology department.

4) Repeating meta-group existence test

SQL provides a Boolean function that tests whether duplicate tuples exist in the results of a subquery. If there are no duplicate tuples in the subquery results as parameters, the unique structure returns TRUE. (This structure is not widely implemented and SQL SERVER2012 is not supported)

Consider the query "find out all the courses that are offered up to one time in 2009", as follows:

Select t.course_id

From course as T

Where unique (select R.course.id

FROM clause as R

where t.course_id=r.course_id and r.year = 2009);

If you do not use unique, the following:

Select t.course_id

From course as T

where 1>=(select count (r.course.id)

FROM clause as R

where t.course_id=r.course_id and r.year = 2009);

We can test the existence of duplicate tuples in a subquery with the not unique structure.

Consider the query "find all courses that have been opened at least two times in 2009"

Select t.course_id

From course as T

Where not unique (select R.course.id

FROM clause as R

where t.course_id=r.course_id and r.year = 2009);

　　

SQL Learning Note 4--nested subqueries (top)