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
INNER JOIN [Group] ON
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
You can use NOT EXISTS
to get Customer
not in specific Group
:
SELECT *
FROM Customer c
WHERE
NOT EXISTS(
SELECT 1
FROM GroupCustomer
WHERE
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.
source to share
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.
source to share