SQL Server Performance Forum – Threads Archive

Long Between

Here’s the deal. I have one table with names and ID’s… UserTable: UserID, UserName Then I have a table of past selections, what I
mean is that for certain reasons we keep the first and last
UserID selected: SelectTable: FirstUserID, LastUserID Now I want to make a selection from the UserTable of people that we have not selected before. (There are many SelectTable Rows) I need to do something like this: Select UserTable.* From UserTable Where UserID IS
NOT between SelectTable.FirstUserID And SelectTable.LastUserID That is my Pseudo code, I understand that doesn’t work but any help would greatly appreciated. Thanks,
Brian Linden

try… select UserID from UserTable where (select count(*) from SelectTable where UserID >= FirstUserID and UserID <= LastUserID) = 0

Brian – If you are trying to get random records try this:
SELECT UserTable.*
FROM UserTable
ORDER BY NEWID()
This works with SQL 2000. For your query to work just remove the IS SELECT UserTable.*
FROM UserTable
WHERE UserID NOT BETWEEN SelectTable.FirstUserID AND SelectTable.LastUserID Bambola.

Chappy, select UserID from UserTable where not exists (select * from SelectTable where UserID >= FirstUserID and UserID <= LastUserID) will perform faster than doing a COUNT as in your code. It will only access one row that not matches the criteria, where a COUNT will access all rows that not match the criteria. (Hey, this is a performance forum <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />)

In this case, Jacco, I’d go with
select UserID from UserTable where not exists (select NULL from SelectTable where UserID >= FirstUserID and UserID <= LastUserID)
Since the subquery returns TRUE or FALSE, and you don’t really need to select any field. Bambola.

Hi Bambola, Using * or NULL or a column name or even a constant (0, 1 etc) has the same effect in a Select clause that is used inside an Exists. It is just a placeholder. Run the following in Query Analyzer and look at the execution plans:
IF EXISTS (SELECT * FROM orders where orderid = 9)
SELECT 1 IF EXISTS (SELECT NULL FROM orders where orderid = 9)
SELECT 1 IF EXISTS (SELECT 1 FROM orders where orderid = 9)
SELECT 1 IF EXISTS (SELECT shipvia FROM orders where orderid = 9)
SELECT 1 IF EXISTS (SELECT * FROM orders where employeeid = 9)
SELECT 1 IF EXISTS (SELECT NULL FROM orders where employeeid = 9)
SELECT 1 IF EXISTS (SELECT 1 FROM orders where employeeid = 9)
SELECT 1 IF EXISTS (SELECT shipvia FROM orders where employeeid = 9)
SELECT 1

You are right, Jacco, they do produce the same query plan [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Reading the post again, I realize that I did not understand it the right way. Oh well…. Bambola.

]]>