SQL复杂视图

Question

我想创建一个视图从MySQL与4个表:

客户

tbody> <<tr>2史密斯

customer_id	lastname
1	和

您希望显示所有订单及其客户和产品。但你也希望结果中没有订单的客户和没有订单的产品。最简单的方法是通过单独的查询结果，您将其与UNION ALL粘合在一起。

SELECT c.lastname, o.order_date, p.ref as product_ref, p.price, po.quantity
FROM `order` o
JOIN customer c ON c.customer_id = o.customer_id
JOIN product_to_order po ON po.order_id = o.order_id
JOIN product p ON p.product_id = po.product_id
UNION ALL
SELECT lastname, null, null, null, null
FROM customer
WHERE customer_id NOT IN (select customer_id FROM `order`)
UNION ALL
SELECT null, null, p.ref, p.price, null
FROM product
WHERE product_id NOT IN (select product_id FROM product_to_order)