Exist and not exist usage

Reference: http://wenku.baidu.com/view/577f4d49cf84b9d528ea7a6f.html // This lecture is very detailed

Reference from: http://chenling1018.blog.163.com/blog/static/14802542007112944944177/

-- Exists (SQL return result set, true)
-- Not exists (SQL does not return result set, true)
-- 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

-- The relationship between table A and table B is 1-to-many. A. ID => B. Aid

Select ID, name from a where exist (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 has a value and returns true, so data exists.

Select ID, name from a where exists (select * from B where B. Aid = 2)
---> Select * from B where B. Aid = 2 has a value and returns true, so data exists.

Select ID, name from a where exists (select * from B where B. Aid = 3)
---> Select * from B where B. Aid = 3 no value, returns false, 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)