Google+ Followers

zondag 12 januari 2014

Why I like PostgreSQL more than MySQL: Combining Queries

So you have a shop that sells powerwashers and your flagship product is the PW-100, and a about a week ago you received a shipment of nozzles that fix a known problem in the PW-100. But, this only affects the PW-100 when used with the universal extender EX-900. Your customers are not ordering them as often as you think they should, so you want to send them an email telling them about this new nozzle. Of course you are very much against useless information so you only want to send the email to people who have actually bought the PW-100 and the EX-900, and only if the have not yet ordered the new nozzle.

This sounds like a bunch of INNER and LEFT joins, but PostgreSQL has proper support for EXCEPT and INTERSECT so you can write the query like this:


SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='PW-100'

INTERSECT

SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='PW-100'

EXCEPT

SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='New_nozzle_for_PW-100'

The power of INTERSECT and EXCEPT become more apparent as the queries get more complex. This example selects only on field but of course it works just as well when selecting a list of fields, which would be a pain for a JOIN syntax.