PostgreSQL----in&&exists

I. In && No in

WHERE expression in (subquery)

Inside the right parenthesis is a subquery result set that returns a field, and the left expression (or field) evaluates each row of the query once and compares it, and if there are equal rows in the result set, the in result is ' TRUE ', otherwise ' FALSE ';

WHERE expression not in (subquery)

The not in and in is the opposite, if the result set does not have equal row result of ' TRUE ', otherwise ' FALSE '.

Test table:

test = # \ d tbl_test
   Table "public.tbl_test"
 Column | Type | Modifiers
-------- + --------- + -----------
 f | integer |

test = # \ d tbl_insert
         Table "public.tbl_insert"
 Column | Type | Modifiers
-------- + ----------------------- + -----------
 a | integer |
 b | integer |
 c | character varying (12) |
 
 
 test = # select * from tbl_test;
 f
---
 1
 3
 5
(3 rows)

test = # select * from tbl_insert;
 a | b | c
--- + --- + -------
| 1 | 11
| 2 | 22
| 3 | 33
| 4 | 44
| 5 | 51
| 6 | 1
| 6 | 61
| 6 | 661
| 7 | 3% 1
| 8 | 3% _1
| 8 | 3 _% _ 1
| 7 | abc
| 7 | ABc
| 7 | aBC
(14 rows)
Example 1. Query the tbl_insert table, and the value of the a field in the tbl_test table field f

test = # select * from tbl_insert where a in (select f from tbl_test);
 a | b | c
--- + --- + ----
| 1 | 11
| 3 | 33
| 5 | 51
(3 rows)
Example 2. Query the tbl_insert table, and the value of the a field is less than the value of the tbl_test table field f

test = # select * from tbl_insert where a + 1 in (select f from tbl_test);
 a | b | c
--- + --- + ----
| 2 | 22
| 4 | 44
(2 rows)
Example 3. Query the rows of the tbl_insert table and the value of the a field is not in the f field of the tbl_test table

test = # select * from tbl_insert where a not in (select f from tbl_test);
 a | b | c
--- + --- + -------
| 2 | 22
| 4 | 44
| 6 | 1
| 6 | 61
| 6 | 661
| 7 | 3% 1
| 8 | 3% _1
| 8 | 3 _% _ 1
| 7 | abc
| 7 | ABc
| 7 | aBC
(11 rows)
Example 4. Query the tbl_insert table and the row with a field value equal to 5 or 7

test = # select * from tbl_insert where a in (5,7);
 a | b | c
--- + --- + -----
| 5 | 51
| 7 | 3% 1
| 7 | abc
| 7 | ABc
| 7 | aBC
(5 rows)
2.EXISTS && NOT EXISTS
WHERE EXISTS (subquery)

The parentheses are also a subquery. If the subquery returns a result, the EXISTS result is 'TRUE', otherwise it is 'FALSE'.

 

WHERE NOT EXISTS (subquery)

NOT EXISTS is the opposite of EXISTS. If the subquery returns no results, it is 'TRUE', otherwise 'FALSE'.

 

Example 1. Query the tbl_insert table, and the value of the a field in the tbl_test table field f

test = # select * from tbl_insert where exists (select null from tbl_test where tbl_test.f = tbl_insert.a);
 a | b | c
--- + --- + ----
| 1 | 11
| 3 | 33
| 5 | 51
(3 rows)
Example 2. Query the tbl_insert table, and the value of the a field is not in the tbl_test table field f

test = # select * from tbl_insert where not exists (select null from tbl_test where tbl_test.f = tbl_insert.a);
 a | b | c
--- + --- + -------
| 2 | 22
| 4 | 44
| 6 | 1
| 6 | 61
| 6 | 661
| 7 | 3% 1
| 8 | 3% _1
| 8 | 3 _% _ 1
| 7 | abc
| 7 | ABc
| 7 | aBC
(11 rows)
PS: The efficiency of NOT IN is very low. If possible, NOT EXISTS is recommended.

postgresql ---- IN && EXISTS