Exists () and in () Applications

Exists () and in () Applications 1. example: emp table: Address Numberbj 40bj 30fj 20fj 10www.2cto.com dept table: Bearing AddsNO bjSO fj (1) exists () Application select sum (Number) from emp where exists (select * from dept where Bearing = 'so') the output result is: 100 www.2cto.com does not seem to match. The answer is correct, because exists is used to determine whether exists exist. If the query result in exists (query) returns true, otherwise false. Not exists is the opposite. When exists is used as the where condition, it first queries the primary query before where, and then substitutes the results of the primary query into the query of exists for judgment. If it is true, It outputs the results of the current primary query, otherwise, no output is made. Not exists is opposite to exists. (2) the output result of the in application select sum (Number) from empwhere Address in (select Adds from dept where Bearing = 'so') is: 30 in is used to determine the value contained in a field. Multiple values are allowed in the where clause. Not in is an excluded value. Syntax: select * from table 1 where field 1 in (val, val ,.....) www.2cto.com select * from empwhere adds not in (select Adds from dept where Bearing = 'so '); select * from empwhere not exists (select Adds from dept where Bearing = 'so' and emp. address = dept. adds) 2. the Efficiency Value of in and exists is not absolute. In is suitable for the select * from emp where deptid in (select id from dept) of small external tables to: select * from emp, (select distinct id from dept) dept where emp. deptid = dept. id; exists is suitable for select * from emp where exists (select id from dept where dept. id = emp. deptid) to: www.2cto.com for deptid in (select * from emp) loop if (exists (select id from dept where id = emp. deptid) then output the record! End if end loop