Usage of exists,not exists in SQL

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, the EXISTS 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 result set is 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 where EXISTS (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 where EXISTS (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 where EXISTS (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 where EXISTS (SELECT * from B where b.aid=3)
--->select * from B WHERE b.aid=3 No value returns true so no data

Not EXISTS is the reverse.
Select Id,name from A where isn't EXIST (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 = No 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 of exists,not exists in SQL