SQL exists, not exists usage

Exists: whether to return the result set. You do not need to know what to return, for example:
Select name from student where sex = 'M' and Mark exists (Select 1 from grade where...), As long
If a result set is returned for the clause guided by exists, the exists condition is true. Note that the returned field is always 1. If it is changed to"Select 2 from grade where...", Then the returned field is 2. This number is meaningless. Therefore, the exists clause does not care about what is returned, but whether a result set is returned.

The biggest difference between exists and in is that in-guided clauses can return only one field, for example:
Select name from student where sex = 'M' and mark in (Select 1, 2, 3 from grade where...)
, The in Clause returns three fields. This is incorrect. The exists clause allows, but the in clause only allows one field to be returned. You can simply enter two fields in 1, 2, and 3.

Not exists and not in are the opposite of exists and in.

Exists (the result set returned by SQL is true)
Not exists (SQL does not return true result sets)

The process of not exists is described in detail below:

As follows:
Table
ID name
1 A1
2 A2
3 A3

Table B
Id aid name
1 1 B1
2 2 B2
3 2 B3

A. ID => B. Aid

Select ID, name from a where exists (select * from B where a. ID = B. Aid)
The execution result is
1 A1
2 A2
The cause 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 A value returns 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 returns true because 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 opposite
Select ID, name from a where not exist (select * from B where a. ID = B. Aid)
The execution result is
3 A3
========================================================== ==========================================
Exists = In, meaning the same, but there is a little difference in syntax, it seems that the efficiency of using in is almost the same, it should be because the index will not be executed
Select ID, name from a where ID in (select aid from B)

Not exists = not in, meaning the same, but there is a little difference in syntax
Select ID, name from a where id not in (select aid from B)

Sometimes we need to select a column that is not repeated. A column is used as the selection condition and other columns are output normally.

Table below:

ID name class count date

1 Apple Fruit 10 2011-7-1

1 orange fruit 20 2011-7-2

1 bananas and fruits 15 2011-7-3

2. Cabbage and vegetables 12 2011-7-1

2 vegetables and vegetables 19 2011-7-2

If you want to get the following result: (the ID is unique and the date is the most recent one)

1 bananas and fruits 15 2011-7-3

2 vegetables and 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 distinct is used, this result is not obtained because distinct is used

Select distinct ID, name, class, Count, date from table

The result is that all different columns in the table are displayed as follows:

1 Apple Fruit 10 2011-7-1

1 orange fruit 20 2011-7-2

1 bananas and fruits 15 2011-7-3

2. Cabbage and vegetables 12 2011-7-1

2 vegetables and vegetables 19 2011-7-2

If you use group by, you cannot get the expected results. Because group by must be used together with the aggregate function, you must use group by or aggregate functions for the name, class, and count columns.

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 bananas and fruits 15 2011-7-3

2. Cabbage and vegetables 12 2011-7-1

2 vegetables and vegetables 19 2011-7-2

If you write

Select ID, max (name), max (class), max (count), max (date)
From table
Group by ID

The result is:

1 bananas and fruits 20

2 vegetables and vegetables 19 2011-7-2

If in is used, the result is sometimes not obtained (sometimes it can be obtained, if the date is not the same (no duplicate data), or the max (date) obtained below has 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: 2011-7---7-3)

1 orange fruit 20 2011-7-2

1 bananas and fruits 15 2011-7-3

2 vegetables and vegetables 19 2011-7-2

Note that only one field in can be returned.

There is one way to achieve this:

Select ID, name, class, Count, date
From Table1 t
Where (date =
(Select max (date)
From Table1
Where id = T. ID ))