sql server - More efficient SQL statement to eliminate my n^2 algorithm? -

let's following sql tables:

my first table called [customer].

customerid    customername    customeraddress ----------    ------------    --------------- 1             name1           1 infinity loop 2             name2           2 infinity loop 3             name3           3 infinity loop 

my next table called [group].

groupid       groupname -------       --------- 1             group1 2             group2 3             group3 

then, link two, have table called [groupcustomer].

groupid    customerid -------    ---------- 1          2 1          3 2          1 3          1 

so on asp.net page, have 2 tables want display. first table customers in particular group. in drop down list, if select group1, display following table:

customerid    customername    customeraddress ----------    ------------    --------------- 2             name2           2 infinity loop 3             name3           3 infinity loop 

the table above customers "associated" selected group (which in case group1). then, in other table, want display this:

customerid    customername    customeraddress ----------    ------------    --------------- 1             name1           1 infinity loop 

essentially, table, want display customers not in selected group.

to generate table customers in selected group, wrote following sql:

select customer.customerid, customer.customername, customer.customeraddress    customer inner join groupcustomer on     customer.customerid = groupcustomer.customerid inner join [group] on     groupcustomer.groupid = [group].groupid [group].groupid = @selectedgroupparameter 

so when mentioned n^2 algorithm, used sql statement above, , compared against sql statement select * customer table. there match, had did not display it. incredibly inefficient, , i'm not proud of.

this leads current question, what's efficient sql statement can write eliminate n^2?

you can use not exists customers not in particular group:

select * customer c     not exists(         select 1         groupcustomer                     customerid = c.customerid             , groupid = @selectedgroupparameter     ) 

read article aaron bertrand different ways solve kind of problem , performance comparisons, not exists being fastest according test.

sql fiddle