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;
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);
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.
- 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
- 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;