Re: postgresql vs mysql

[Peter Eisentraut <peter_e@xxxxxxx>]

Adam Rich wrote:
> -- Query A
> select count(*) from customers c
> where not exists ( select 1 from orders o
> where o.customer_id = c.customer_id )
>
> -- Query B
> select count(*) from customers c
> where customer_id not in ( select customer_id from orders)
>
> -- Query C
> select count(*) from customers c
> left join orders o on c.customer_id = o.customer_id
> where o.order_id is null
>
> I believe they all achieve the same thing.

I think not.  When using

INSERT INTO customers VALUES (1);
INSERT INTO customers VALUES (2);
INSERT INTO customers VALUES (NULL);

and

INSERT INTO orders VALUES (1);
INSERT INTO orders VALUES (3);
INSERT INTO orders VALUES (NULL);

I get

Query A: 2
Query B: 0
Query C: 3

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/