(Turn fly-direction accumulate precipitation http://www.cnblogs.com/mytechblog/)
Usage of exists, not exists in SQL
exists says () a subquery Returns a result that is not NULL. Indicates that the where condition will execute the main SQL statement, and if NULL indicates that the WHERE condition is not true, the SQL statement will not execute. not exists and exists instead, the subquery result is empty, which means that the where condition is true and the SQL statement is executed. Otherwise, it is not executed.
exists : The emphasis is on whether to return the result set and not to know what to return, such as:
Select name from student where sex = ' m ' and Mark exists(select 1 from grade where ...), as long as
exists the boot clause has a result set return, then exists This condition is set up, we notice that the returned field is always 1, if you change to "Select 2 from Grade where ...", then the returned field is 2, This number is meaningless. So the exists clause does not care what is returned, but rather whether there is a result set returned.
The biggest difference between exists and in is that the in-boot clause can only return a single field, such as:
Select name from student where sex = ' m ' and mark in (select-from-grade where ...)
, the IN clause returns three fields, which is incorrect, theexists clause is allowed, but in only one field is allowed to return, and two fields can be randomly removed in the three-way.
Not exists and not are the opposite of exists and in.
exists (SQL returns the result set as true)
not exists (SQL does not return a result set to true)
The following is a detailed description of the process of not exists :
As follows:
Table A
ID NAME
1 A1
2 A2
3 A3
Table B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
Table A and table B are 1-to-many relationships a.id = B.aid
SELECT Id,name from A WHEREEXISTS(SELECT * from B WHERE a.id=b.aid)
Execution results are
1 A1
2 A2
The reasons can be analyzed as follows
SELECT Id,name from A WHEREEXISTS(SELECT * from B WHERE b.aid=1)
--->select * from B WHERE b.aid=1 has value return true so there is data
SELECT Id,name from A WHEREEXISTS(SELECT * from B WHERE b.aid=2)
--->select * from B WHERE b.aid=2 has value return true so there is data
SELECT Id,name from A WHEREEXISTS(SELECT * from B WHERE b.aid=3)
--->select * from B WHERE b.aid=3 No value returns true so no data
notEXISTSis the reverse.
SELECT Id,name from A WHERE notEXIST (SELECT * from B WHERE a.id=b.aid)
Execution results are
3 A3
===========================================================================
===========================================================================
EXISTS = in, same meaning but slightly different in syntax, as if using in efficiency to almost, should be the reason why the index is not executed
Select Id,name from A WHERE ID in (select AID from B)
not EXISTS = not in, same meaning but slightly different in syntax.
Select Id,name from A WHERE ID not in (SELECT AID from B)
Sometimes we run into situations where you want to select a column that does not repeat, a column as a selection condition, and the other columns to output normally.
Table tables are as follows:
Id Name Class Count Date
1 apple Fruit 10 2011-7-1
1 Orange Fruit 20 2011-7-2
1 Banana Fruit 15 2011-7-3
2 Cabbage Vegetables 12 2011-7-1
2 green vegetables 19 2011-7-2
If you want to get the following result: (ID unique, date selected last)
1 Banana Fruit 15 2011-7-3
2 green vegetables 19 2011-7-2
The correct SQL statement is:
SELECT Id, Name, Class, Count, Date
From table T
WHERE ( not EXISTS
(SELECT Id, Name, Class, Count, Date from table
WHERE Id = t.id and Date > T.date))
If you use DISTINCT, you cannot get this result, because distinct is a function of all columns
SELECT DISTINCT Id, Name, Class, Count, Date from table
The result is that all the different columns of the table tables are displayed as follows:
1 apple Fruit 10 2011-7-1
1 Orange Fruit 20 2011-7-2
1 Banana Fruit 15 2011-7-3
2 Cabbage Vegetables 12 2011-7-1
2 green vegetables 19 2011-7-2
If you do not get the desired result with group by, because group by is used in conjunction with the aggregate function, either group by is used for the Name,class and Count columns, or aggregate functions are used. If written
SELECT Id, Name, Class, Count, MAX (Date)
From table
GROUP by Id, Name, Class, Count
The result is
1 apple Fruit 10 2011-7-1
1 Orange Fruit 20 2011-7-2
1 Banana Fruit 15 2011-7-3
2 Cabbage Vegetables 12 2011-7-1
2 green vegetables 19 2011-7-2
If written
SELECT Id, Max (Name), Max (Class), Max (Count), Max (Date)
From table
GROUP by Id
The resulting results are:
1 Banana Fruit 20 2011-7-3
2 green vegetables 19 2011-7-2
If you do not get results with in, sometimes you can get, if the date is not the same (there is no duplicate data), or if you get the following Max (Date) only one value)
SELECT DISTINCT Id, Name, Class, Count, Date from table
WHERE (Date in
(SELECT MAX (Date)
From table
GROUP by Id))
The result is: (Because Max (Date) has two values of 2011-7-2,2011-7-3)
1 Orange Fruit 20 2011-7-2
1 Banana Fruit 15 2011-7-3
2 green vegetables 19 2011-7-2
Note In only one field is allowed to return
There is a way to achieve:
SELECT Id, Name, Class, COUNT, Date
From table1 t
WHERE (Date =
(SELECT MAX (Date)
From table1
WHERE Id = t. ID))
Usage and difference of oracle_not exists and not in