1. Set operation

Use a Venn's diagram to illustrate what set operation is.

1.1 intersection union

give an example:

SELECT product_id, product_name
  FROM Product
 UNION
SELECT product_id, product_name
  FROM Product2;

1.1.1 union equivalent conversion

The results under different conditions of the same table can be converted with union (or - > union)
give an example:

-- use OR predicate
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;

-- use UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3
 UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price IS NULL;

1.1.2 union all

-- Keep duplicate lines
SELECT product_id, product_name
  FROM Product
 UNION ALL
SELECT product_id, product_name
  FROM Product2;

1.2 intersection

MySQL 8.0 does not support intersect in SQL server. However, it can be realized through sub query and where qualification.
Example: query the product names common to two tables

select product_id,product_name
from product as p
where p.product_name  in (select product_name
from produccopy);

1.3 difference set

MySQL 8.0 does not support the intersection statement except in SQL server. However, it can be realized through sub query and where qualification.
For example, query the unique products in the product table

select product_id,product_name
from product
where product_id  not in (select product_id
from produccopy);

1.4 symmetry difference

What is symmetry difference? The symmetry difference between two sets A and B refers to the set composed of elements only belonging to A or B, that is, the unique elements of each set are combined together.
For example, table product and table shopproduct are unique to each other

select product_id,product_name
from product
where product_id  not in (select product_id
from shopproduct)
union
select product_id,product_name
from shopproduct
where product_id  not in (select product_id
from product);

2. Connection

The effect of connection is shown as follows:

It is simply to connect table A and table B together, but the connection often requires common attributes (on connection conditions). The advantage of connecting together is to display the unique information in different tables. The connected tables are disposable, that is, they are erased from the storage after use.

2.1 internal connection

Inner link is a combination of table information that meets relevant conditions

-- Inner link
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

Here on is equivalent to where in select and having in group by.
be careful

1. The premise is to link different tables through the hub condition of on. The linked tables can be regarded as one table. You can use select to query their properties and filter where
2. Before joining, you can set filter conditions, select the required conditions, and then join the tables together

For example, the common link is through product_ The common attribute ID links the two tables together

SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;

For example, filter information before linking

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- Subquery 1:from ShopProduct The table filters out the information of Tokyo stores
        SELECT *
          FROM ShopProduct
         WHERE shop_name = 'Tokyo' ) AS SP
 INNER JOIN -- Subquery 2:from Product The table filters out the information of clothing products
   (SELECT *
      FROM Product
     WHERE product_type = 'clothes') AS P
    ON SP.product_id = P.product_id;

2.2 external connection

As the literal meaning, the outer link has the opposite effect to the inner link. If the conditions meet table A but not table B (or vice versa or neither), the two tables are connected together. For example, A left join saves rows in the left table that cannot be matched according to the ON clause. At this time, the rows corresponding to the right table are missing values, that is, the left table is placed in A row in the linked table, and the information of the right table in this row is null. Right link, opposite to left link.

give an example,

select p2.shop_id,p2.shop_name,p2.product_id,p2.quantity,
p1.product_name,p1.sale_price
from product as p1
left join shopproduct as p2
on (p1.product_id = p2.product_id )
where sale_price >500
order by quantity;

product table
shopproduct table