Sub-query and Connection usage guide

A subquery is a query that is nested within a query. Nested series with the database manufacturers of different settings, the general maximum number of nested not more than 15, the actual application, generally do not exceed 2 levels, otherwise the code is difficult to understand. In general, all nested subqueries can be rewritten as not nested queries. But that will increase the amount of code. Subqueries, like recursive functions, are sometimes used to achieve a multiplier effect, but their execution efficiency is also lower, sometimes using their own connection can replace some subqueries, in addition, some related subqueries can be written as unrelated subqueries.

Subqueries are often used in complex SQL operations, including select,insert,delete,update and so on. Subqueries can be divided into two types: correlated subqueries and unrelated subqueries, as the name suggests, the related subqueries cannot run alone, You must rely on external parent queries to provide certain values to run.

Subqueries can return: result set, logical value. Only when the EXISTS clause is used does the subquery return a result set and only logical values such as TRUE or FALSE. The keywords available for subqueries are: In,any (either), all, and so on. Specifically, subqueries can return single values, tuples ( That is, two or more than two values), and a multivalued result set.

Here are some examples to illustrate the use of subqueries:

1. Assuming that there are empid and empname two fields in the data table EMP, where empid (primary key) is duplicated, it is now required that the Empid value in the table be deleted with duplicate records, and a record with a Empid value duplicate must be kept.

Solution: With the relevant subquery and its own connection, the following is the implementation of the SQL statement

Delete from EMP E1 where EmpID into (select EmpID from emp where EmpID = E1.empid and EmpName!= e1.empname)

2. Use subqueries to update database tables. Suppose that there are 2 fields in the datasheet salary empid and Salaryamount, and there are 2 fields in the data table emp Empid and age. Use the Age field in the EMP table instead of the value of the Salaryamount field in the salary table, which is the SQL statement that you implemented

Update salary S Set salaryamount = (select age from emp where EmpID = s.empid)

Note: You can also use field values that do not appear in parent queries in (related) subqueries, such as the Empid field in the previous example

3. Connection: The connection is divided into 4 types, namely the inner join (inter join), the outer join (outer join, divided into left, right and outer three kinds), the cross join (cross join) and its own connection (self join). The keywords and identifiers for outer joins are: left, Right,full, (+), * etc. when identified by (+), the table near (+) is the secondary table, while the table away from (+) is the primary table (this is dependent on the database implementation, as specified in Oracle)

4. Suppose there are Customers table customer, where the fields have CUSTID,CUSTNAME,ADDR, customer order form orders, where the field has orderid,custid,orderdate, ProcID. Please check out the customers who have orders after January 1, 2000 and all the customers (no orders)

Select custid,custname,addr,orderdate from customer left join orders on

(Customer.custid = Orders.custid) and (OrderDate > ' 2000-1-1 ')

Description: Left outer joins, the table on the left is the main table, in this case, the table customer is the primary table, the right outer join, then the table on the right is the primary table. If you do not need to sit outside the connection, the customer who did not order before January 1, 2000 is not available. In general, if you want to keep any records out of the query, you only have to use a full outer join.

5. The 6 keywords of the query are: Select,from,where,group By,having,order by, and the above is the order in which they should normally appear in the query statement. Having a relationship with group by is like the relationship between where and select , which are used to qualify which rows are selected. Here are some caveats: you cannot sort in a subquery, that is, you cannot have an ORDER BY clause the field in a by clause may not appear in the field list after the SELECT clause, but when there is a distinct qualifier, the order The fields in by by must appear in the SELECT clause. Subqueries can be nested and started from inside Out

6. Suppose there is a student table student, which has field sno,sname,birthday, curriculum course, which has fields cno,cname,ccent, students choose the Schedule SC, where there are fields Cno,sno,grade please check out the students who have not selected any class

Select Sno,sname from student S where not exists

(select Sno from SC where CNO

(select CNO from course where CNO = sc.cno and Sc.sno = S.sno)

) To inquire about the course number and course name not taken by any student

Select Cno,cname from Course where CNO (select CNO from SC) to remove courses not taken by any student

Delete from course where CNO in (select CNO from course where CNO does not

(select CNO from SC)

)

7. And union, intersect, minus calculation Please check out the list of students who have selected more than 2 courses, such as English and Chinese

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' English ')

)

Union

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' Chinese ')

Check out the list of students who have taken English without elective Chinese

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' English ')

)

Minus

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' Chinese ')

Check out the list of students who have both English and Chinese

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' English ')

)

Intersect

SELECT * FROM student where Sno

(Select Sno from SC

where CNO in

(select CNO from course where cname = ' Chinese ')

)

8. Delete or modify clauses cannot add join conditions, such as: delete from table1 where condition1 and condition2 statements such as this are illegal, that is, condition1 conditions cannot have condition2 or more conditions, The solution can be a subquery, such as:

Delete from table1 where (...) and similarly to the UPDATE clause

9. The inner connection is an equivalent connection, so when the two tables do not match, it is easy to miss the data, the solution is to use the external connection, but whether the left or right outside the connection, in theory, can only make missing data less, only the entire external connection to ensure that no missing data.

You need to be aware of the effects of NULL (NULL) on (sub) queries, such as the following example, if you want to query the rank of the same ID in table A and B, you must qualify the ID not to be null, or the subquery will return an empty result set:

Select A.id,a.level,a.desc from table1 A

where A.level in

(select B.level from Table2 B where

(a.ID = b.ID and b.id is not null)

)