mysql中实现并集、交集、差集、内联结、外联结、3张以上表的联结、交叉联结

一、mysql中实现并集

1.union函数

1.1

mysql> select product_id,product_name from product union select product_id,product_name from product2;

1.2order by只能使用一次，而且在最后使用

mysql> select product_id,product_name from product where product_type='厨房用具' union select product_id,product_name from product2 where product_type='厨房用具' order by product_id;

1.union  all函数

mysql> select product_id,product_name from product union all select product_id,product_name from product2;

二、mysql中实现交集

1.INTERSECT函数无法在MySQL中使用，只能在orcal中使用

2.利用in实现交集

mysql> select product_id,product_name from product where product_id in(select product_id from product2);

三、mysql中实现差集

1.EXCEPT函数无法在MySQL中使用，只能在orcal中使用

2.利用not in实现差集

mysql> select product_id,product_name from product where product_id not in(select product_id from product2);

四、mysql中实现内联结

内联结和WHERE子句结合使用

五、mysql中实现外联结

六、3张以上表的联结

mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price,ip.inventory_quantity from ShopProduct as sp INNER JOIN product as p ON sp.product_id = p.product_id INNER JOIN InventoryProduct as ip ON sp.product_id = ip.product_id where ip.inventory_id='p001';

七、交叉联结

交叉联结的集合运算符是    笛卡儿积

mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name from ShopProduct as sp CROSS JOIN product as p;