CODEWITHSUNDEEP
sql-server
Fabio
Fabio

Reputation: 32445

Return all rows if joined table contains no rows, otherwise return only matched rows

Database structure

CREATE TABLE SalesOrder
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Reference NVARCHAR(50) NOT NULL DEFAULT '',
    CustomerID INT NOT NULL DEFAULT 0
);
CREATE NONCLUSTERED INDEX IX_SalesOrder_CustomerID ON SalesOrder (CustomerID);

CREATE TABLE Customer
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL DEFAULT ''
);

Example: SQL Fiddle

Table-valued variable @SelectedCustomers can be empty or containing list of customer's ID selected by user.
This query return orders of the selected customers and returns nothing if selected list is empty

SELECT so.ID
, so.Reference
, c.Name AS CustomerName
FROM SalesOrder so
INNER JOIN Customer c ON c.ID = so.CustomerID
WHERE so.CustomerID IN (SELECT ID FROM @SelectedCustomers)

But I need to return all rows if variable is empty
I tried OR keyword, but this approach isn't index friendly and query become very slow.

Currently I generate query in the application's code(.NET).
If list contains some values, then
WHERE so.CustomerID IN (SELECT ID FROM @SelectedCustomers) clause added to the query

Question: Does exists some other way to get needed result in more efficient way?

Upvotes: 1

Views: 84

Answers (3)

paparazzo
paparazzo

Reputation: 45096

Not the same
As this would return if @SelectedCustomers had value(s) but not matching 

SELECT so.ID
     , so.Reference
     , c.Name AS CustomerName
FROM SalesOrder so
INNER JOIN Customer c ON c.ID = so.CustomerID 
LEFT  JOIN @SelectedCustomers s on so.CustomerID = s.ID 
WHERE s.ID is null

a union might work 

SELECT so.ID
     , so.Reference
     , c.Name AS CustomerName
 FROM SalesOrder so
JOIN Customer c ON c.ID = so.CustomerID 
JOIN @SelectedCustomers on so.CustomerID = @SelectedCustomers.ID 
union 
SELECT so.ID
     , so.Reference
     , c.Name AS CustomerName
FROM SalesOrder so
JOIN Customer c ON c.ID = so.CustomerID 
where (select top 1 ID from @SelectedCustomers) is null

Or I know it sounds crazy but if no rows in @SelectedCustomers then insert all distinct ID. You get out of the OR.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415715

Not sure, but this might work:

SELECT so.ID
    , so.Reference
    , c.Name AS CustomerName
FROM SalesOrder so
INNER JOIN Customer c ON c.ID = so.CustomerID
INNER JOIN @SelectedCustomers s on c.ID = COALESCE(s.ID, c.ID)

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

Add to the where end of the where statement:

Or @selectedcustomer is null

Upvotes: 0

Related Questions