mysql notes(4)

The where statement can be used with AND and OR, and parentheses should be used when a clear order is required.

A comma-separated list of legal values ​​for IN, all enclosed in parentheses. IN function is the same as OR, but faster and concise than OR

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id IN (1002,1003)
ORDER BY prod_name ;
Insert picture description here

You can use NOT to cooperate with IN BETWEEN EXISTS for negation operation.

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name ;
Insert picture description here
SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id NOT BETWEEN 1001 AND 1002
ORDER BY prod_name ;
Insert picture description here

For checking EXISTS subqueries whether at least one row of data is returned, the subquery does not actually return any data, but returns the value True or False
EXISTS specify a subquery, detecting the presence of the line.

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1001);
Insert picture description here
SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1009);

Use NOT to negate exists.

Insert picture description here
SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND NOT EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1009);
Insert picture description here