[MySQL] JOIN 활용

Table 1 = "products_last"

 

Table 2 = "order_last"

 

 

1) SELECT * FROM products_last p RIGHT JOIN orders_last o ON o.product_id= p.id;

2) SELECT p.id, SUM(p.price * o.quantity) AS total_sales FROM products_last p INNER JOIN orders_last o ON p.id = o.product_id GROUP BY p.id ORDER BY total_sales DESC LIMIT 1;

3) SELECT p.id, SUM(o.quantity) AS total_quantity FROM products_last p INNER JOIN orders_last o ON p.id = o.product_id GROUP BY p.id;

4) SELECT p.name FROM products_last p RIGHT JOIN orders_last o ON o.product_id= p.id WHERE o.order_date > '2023-03-03';

5) SELECT p.name, sum(o.quantity) FROM products_last p RIGHT JOIN orders_last o ON o.product_id= p.id GROUP BY p.id ORDER BY o.quantity DESC LIMIT 1;

6) SELECT p.id, avg(o.quantity) FROM products_last p RIGHT JOIN orders_last o ON o.product_id= p.id GROUP BY p.id;

7) SELECT p.id, p.name FROM products_last p LEFT JOIN orders_last o ON o.product_id= p.id WHERE o.id IS NULL;