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

Let's say the following SQL tables:

My first table is called [Customer]


CustomerID    CustomerName    CustomerAddress
----------    ------------    ---------------
1             Name1           1 Infinity Loop
2             Name2           2 Infinity Loop
3             Name3           3 Infinity Loop


My next table is called [Group]


GroupID       GroupName
-------       ---------
1             Group1
2             Group2
3             Group3


Then, to link the two, I have a table called [GroupCustomer]


GroupID    CustomerID
-------    ----------
1          2
1          3
2          1
3          1


So, in an ASP.NET page, I have two tables that I want to display. The first table is, in fact, all the Clients who are in a certain group. So in the dropdown, if I select Group1

, it will display the following table:

CustomerID    CustomerName    CustomerAddress
----------    ------------    ---------------
2             Name2           2 Infinity Loop
3             Name3           3 Infinity Loop


The table above is for all clients that are "linked" to the selected group (which in this case is Group1). Then, in another table, I want it to display this:

CustomerID    CustomerName    CustomerAddress
----------    ------------    ---------------
1             Name1           1 Infinity Loop


Essentially, for this table, I want it to display all customers that are NOT in the selected group.

To create a table for all clients in the selected group, I wrote the following SQL:

SELECT Customer.CustomerID, Customer.CustomerName, Customer.CustomerAddress   
FROM Customer
INNER JOIN GroupCustomer ON
    Customer.CustomerID = GroupCustomer.CustomerID
    GroupCustomer.GroupID = [Group].GroupID
WHERE [Group].GroupID = @selectedGroupParameter


So when I mentioned my n ^ 2 algorithm, I was essentially using the above SQL statement and comparing it to an SQL statement where I am just SELECT *

from a table Customer

. Where there was a match, I just simply did not see it. It's incredibly ineffective and I'm not proud.

This leads to my current question, what is the most efficient SQL statement I can write that will eliminate my n ^ 2?


source to share

2 answers

You can use NOT EXISTS

to get Customer

not in specific Group


FROM Customer c
        SELECT 1
        FROM GroupCustomer
            CustomerID = c.CustomerID
            AND GroupID = @selectedGroupParameter


Read this article Article Aaron Bertrand for different ways to solve this problem, and their comparisons with the performance NOT EXISTS

is the fastest in accordance with its test.

SQL Fiddle



Select * from Customer
where CustomerID not in
  (select CustomerID 
  from GroupCustomer 
  where GroupID = @selectedGroupParameter)


You can use not in

for this check. However, you can probably just get rid of the join on the Group table for better performance, since you are not actually using the group name.



All Articles