SQL Notes-eighth chapter, sub-query

A. Scalar subquery in the select list

Query for the earliest published books in each type of book. In a SQL query, you need to compare the publication year of a book with the year of publication for all books of that type, and only return a record if they match

SELECT T_category.fid, T_book. Fname,t_book.fyearpublished
From T_category
INNER JOIN T_book on T_category.fid=t_book.fcategoryid
WHERE t_book.fyearpublished=
(
SELECT MIN (t_book.fyearpublished)
From T_book
WHERE T_book.fcategoryid=t_category.fid
)

two. Set operators and subqueries

The requirements for sub-queries of scalar quantum queries are very high, and in many cases the query results are not as scalar quantum queries. If the subquery is a multi-row, multi-column table subquery, then it can be thought of as a temporary data table use, and if the subquery is a multi-row column table subquery, the result set of such a subquery is actually a collection, and SQL provides operators to manipulate such a collection, including in, any, All and exists, etc.

1.IN operator

SELECT * from T_reader
WHERE fyearofjoin in
(
Select fyearpublished from T_book
)

2.ANY and some operators

SELECT * from T_reader
WHERE Fyearofjoin = any
(
Select fyearpublished from T_book
)

In other words, "=any" is equivalent to the in operator, while "<>any" is equivalent to the not-in operators. In addition to the Equals operator, the any operator can be used in conjunction with comparison operators greater than (>), less than (<), greater than or equal (>=), less than or equal (<=). For example, the following SQL statement is used to retrieve books published before any member is Born:
SELECT * from T_book
WHERE Fyearpublished<any
(
SELECT Fyearofbirth from T_reader
)

3.ALL operator

Search for books published before all members join:

SELECT * from T_book
WHERE Fyearpublished<all
(
SELECT Fyearofjoin from T_reader
)

3.EXISTS operator

Unlike the in, anything, all operators, the EXISTS operator is the monocular operator and does not match the column, so it does not require that the collection to be matched be single-column. The EXISTS operator is used to check if each row matches a subquery, and it can be assumed that exists is used to test whether the result of the subquery is empty, and if the result set is empty, the matching result is false, otherwise the match result is true.

SELECT * from T_book
WHERE EXISTS
(
SELECT * from T_reader
WHERE fprovince= ' Shandong '
)

This SQL statement matches each row of data in the T_book table to test for the presence of a reader in Shandong province because the system has readers in Shandong province, so this SQL statement will retrieve all the books.

SELECT * from T_category
WHERE EXISTS
(
SELECT * from T_book
WHERE T_book. Fcategoryid = T_category.fid
and T_book. fyearpublished<1950
)

In a subquery after exists, SQL matches each row of data in the T_category table to a subquery, testing whether there are books in the T_book table that have Fcategoryid field values equal to the current category primary key values and that the year of publication is before 1950.

three. Sub-queries in other types of SQL statements apply

1. Application of sub-query in INSERT statement

Full table Insert

INSERT into  t_ ReaderFavorite2 (Fcategoryid,freaderid)
select&NBSP; fcategoryid,freaderid&NBSP; from &NBSP; T_readerfavorite

INSERT Into T_readerfavorite (Fcategoryid,freaderid)
SELECT 1,fid from T_reader
WHERE Not EXISTS
(
SELECT * from T_readerfavorite
WHERE T_readerfavorite. Fcategoryid=1
and T_readerfavorite. Freaderid= T_reader.fid
)

2. Application of sub-query in UPDATE statement

UPDATE T_book B1
SET B1. fyearpublished=2005
WHERE
(
SELECT COUNT (*) from T_book B2
WHERE B1. Fcategoryid=b2. Fcategoryid
) >3

3. Sub-query application in DELETE statement

DELETE from T_book B1
WHERE
(
SELECT COUNT (*) from T_book B2
WHERE B1. Fcategoryid=b2. Fcategoryid
) >3

SQL Notes-eighth chapter, sub-query