Finding a way to avoid joins/inner selects

[npikes]

In SQL 2005 I'm writing a query that works fine as an inner select or
a join but I know there's gotta be a way to do it without doing either
of those, for obvious performance reasons. I just want to hit the
database once.

Lets say you have a table with 2 columns, users and shipments. A user
can have multiple shipments but those shipments can only be inbound or
outbound so the table would look like this (I'm keeping it very basic
for the sake of this example):

Shipments
User Shipment
1 Inbound
1 Inbound
1 Inbound
1 Inbound
2 Inbound
2 Outbound
2 Inbound
3 Inbound
3 Inbound
3 Inbound
3 Inbound
4 Outbound
4 Outbound
4 Outbound

I want to pull back all users that did not have any outbound
shipments. I know I can do it with an inner select:

select user
from users
where user not in
(
select user
from shipments
where shipment = 'Outbound'
)

This seems so simple but I don't know how I'd do it without hitting
the database twice. Any ideas?

[Plamen Ratchev]

Not sure how much you simplified your scenario, but with the sample data
provided you can use this query to get users who have only Inbound
shipments:

SELECT [user]
FROM Shipments
GROUP BY [user]
HAVING MAX(shipment) = 'Inbound';

As alternative you can rewrite the query you had using NOT EXISTS:

SELECT [user]
FROM Shipments AS A
WHERE NOT EXISTS(SELECT *
FROM Shipments AS B
WHERE B.[user] = A.[user]
AND B.shipment = 'Outbound');

--
Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

(np****@gmail.com) writes:

I want to pull back all users that did not have any outbound
shipments. I know I can do it with an inner select:

select user
from users
where user not in
(
select user
from shipments
where shipment = 'Outbound'
)

This seems so simple but I don't know how I'd do it without hitting
the database twice.

Who says that you hit it twice? Keep in mind that SQL is a declarative
language. You state your intent, the optimizer finds out the best way
do it. OK, so in practice it does matter quite a bit how your queries,
but there are also a lot of cases where the optimizer will implement
your query in a different way.

Which is the best way to express a query, is often difficult to tell
beforehand. When you have different ways to go, benchmarking is
about compulsory. But even then you need to be careful, because what
is true for 10000 rows and one distribution, may not be true for 10
million rows and another distribution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[npikes]

Thank you guys for all your help. I will try different methods and
check the execution plans.