SQL learning | set operation

Addition of tables –UNION

union Simple use , Add up the results of the two queries ：

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

• UNION It will automatically remove the duplicate data in the two query results .
• Want to make UNION Do not automatically de duplicate data , have access to UNION ALL
• When querying in the same table , Use or Often available and used union Same effect , But sometimes for efficiency reasons union. When it comes to the contents of two tables, you have to use UNION 了 .
• Use UNION When querying, columns of the same type are usually put together , But if it's a different type , You can also display two columns of different types in one column through implicit type conversion .

Cross operation –INTERSECT

mysql 8.0 Intersection operations are not supported . It is required that the intersection operation can use inner join For those .
Delivery calculation can also be used AND To achieve .

Difference set , Complement and subtraction of tables

standard SQL The subtraction operator in the table by EXCEPT, however mysql8.0 It also doesn't support . have access to not in Realization except The same effect ：

--  Use  NOT IN  The implementation of clause exists in Product  But not here. Product2  The records in the table 
SELECT
	* 
FROM
	Product 
WHERE
	product_id NOT IN ( SELECT product_id   FROM Product2 )

Symmetry difference

Two sets A,B The symmetry difference refers to those that belong only to A Or only belong to B A collection of elements .

--  Use  NOT IN  Implement the difference set of two tables 
SELECT
	* 
FROM
	Product 
WHERE
	product_id NOT IN ( SELECT product_id FROM Product2 ) UNION
SELECT
	* 
FROM
	Product2 
WHERE
	product_id NOT IN ( SELECT product_id FROM Product )

coupling

Internal connection

Connect store information and commodity information through internal connection query ：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	p.product_name,
	p.product_type,
	p.sale_price,
	sp.quantity   
FROM
	shop_product AS sp  
	INNER JOIN product AS p ON sp.product_id = p.product_id;

Use the sub query method to get... In the Tokyo store , The price is lower than 2000 The product information of ：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	sp.quantity,
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	shop_product AS sp
	INNER JOIN ( SELECT product_id, product_name, product_type, sale_price FROM product WHERE sale_price < 2000 ) AS p ON sp.product_id = p.product_id 
WHERE
	sp.shop_name = ' Tokyo ';

Self coupling

A table that connects itself with itself is self connection .
as follows , Find products that sell for more than the average price of such products

SELECT
	P1.product_id,
	P1.product_name,
	P1.product_type,
	P1.sale_price,
	P2.avg_price 
FROM
	product AS P1
	INNER JOIN ( SELECT product_type, AVG( sale_price ) AS avg_price FROM product GROUP BY product_type ) AS P2 ON P1.product_type = P2.product_type 
WHERE
	P1.sale_price > P2.avg_price;

natural join

Natural join performs equivalent inner join according to the column names contained in both tables .

SELECT
	* 
FROM
	shop_product
	NATURAL JOIN product

The above statement queries all the columns in the two tables . The common columns of the two tables are placed first .

Extraneous junction

According to which table the reserved rows are located , There are three forms of external links : Left link , Right link and all outer link .

The left link will be saved and cannot be followed in the left table ON Clause matches to the line , At this time, the rows corresponding to the right table are all missing values ; The right link will save the right table, which cannot be followed ON Clause matches to the line , At this time, the rows corresponding to the left table are all missing values ; The total external join will save two tables at the same time ON Clause matches to the line , The corresponding row in another table is filled with missing values .

The corresponding grammars of the three external links are :

--  Left link  
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
--  Right link  
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
--  All external links 
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

Non equivalent connection

on The connection conditions of words and sentences are not necessarily =, Including comparison operators (<,<=,>,>=, BETWEEN) And predicate operations (LIKE, IN, NOT wait ) All logical operations including can be placed in ON Clause as a join condition .
Yes Product The goods in the table are ranked according to the selling price ：

SELECT
	product_id,
	product_name,
	sale_price,
	COUNT( p2_id ) AS my_rank 
FROM
	(
	SELECT
		P1.product_id,
		P1.product_name,
		P1.sale_price,
		P2.product_id AS P2_id,
		P2.product_name AS P2_name,
		P2.sale_price AS P2_price 
	FROM
		Product AS P1
		LEFT OUTER JOIN Product AS P2 ON P1.sale_price <= P2.sale_price 
	) AS X 
GROUP BY
	product_id,
	product_name,
	sale_price 
ORDER BY
	my_rank;

give the result as follows ：

The function of sub query is to query each commodity , Find out everything that sells for no less than it , give the result as follows ：

What is used in the subquery is the non equivalent join , Find out all the products of this product that are higher than its selling price , This number is its ranking .

Cross connect –CROSS JOIN( The cartesian product )

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP
 CROSS JOIN Product AS P;

Use cross join Get the Cartesian product of two tables .

Homework

4.1

SELECT
	*
FROM
	product 
WHERE
	sale_price > 500 UNION
SELECT
	* 
FROM
	product2 
WHERE
	sale_price > 500

4.2

SELECT
	* 
FROM
	product 
WHERE
	product_id IN ( SELECT product_id FROM product2 )

4.3

SELECT
	sp.product_id,
	sp.shop_name,
	p.product_name
FROM
	shop_product AS sp
	INNER JOIN (
	SELECT
		product_id, product_name
	FROM
		product AS p1 
	WHERE
		sale_price =(
		SELECT
			max( sale_price ) 
		FROM
			product AS p2 
		WHERE
			p1.product_type = p2.product_type 
		GROUP BY
		product_type 
	)) AS p ON sp.product_id = p.product_id;

Use the associated sub query to find out the products with the highest selling price in each category id, And then use id As a condition of connection with shop_product Table join .

4.4

--  Internal connection mode 
SELECT
	p.product_type,
	p.product_name,
	mp.max_price 
FROM
	product p
	INNER JOIN ( SELECT product_type, MAX( sale_price ) AS max_price FROM product GROUP BY product_type ) AS mp ON ( p.product_type = mp.product_type AND p.sale_price = mp.max_price );

--  Associated sub query method 
SELECT
	product_id,
	product_name,
	product_type
FROM
	product AS p1 
WHERE
	sale_price =(
	SELECT
		max( sale_price ) 
	FROM
		product AS p2 
	WHERE
		p1.product_type = p2.product_type 
GROUP BY
	product_type)

4.5

Reference material ：
[1] https://github.com/datawhalechina/wonderful-sql/blob/main/ch04:%20%E9%9B%86%E5%90%88%E8%BF%90%E7%AE%97.md