Get Rest of Data in Many to Many Relationship

Question

I have customer and group tables, in many to many relationship. For example I have gold group. This group consist of some customer instance. I need list of customer that NOT IN gold member. Simple solution I use this query

Select * from customer where id not in (select customer_id from customer_group where group_id = 1)

This solution to be worst, when my database grow up, thousands customer instance. I need suggestion of an effective approach for this condition.

Answer 1

The where in clause can be very inefficient and heavy on your database and hence should be avoided. (see Is SQL IN bad for performance?)

Instead you should use join for better performance:

SELECT * FROM customer 
LEFT JOIN customer_group
ON customer.customer_id = customer_group.customer_id 
WHERE customer_group.group_id <> 1;

Answer 2

You could use joins instead of subqueries:

 Select * 
 from 
        customer cc
        inner join customer_group grp on grp.customer_id=cc.customer_id
 where 
     grp.group_id <>1

Execution plans - Subqueries costs are bigger.

Answer 3

You can use not exists query instead of where in.

 Select * from customer c 
  where 
 not exists(select customer_id from customer_group g 
      where group_id = 1 and c.id = g.customer_id);

In this we are first finding out the customer in customer_group with same customer_id as c's id and also is a gold member. And if there is no row returned (it means it does not exists) we return all his information from customer table.