Exclude Statement in SQL

Question

How to exclude data from an SQL database using an SQL statement? My situation is I have a user login to their profile page where they will be able to friend people. I want to display all users except themselves that are found in the SQL database.

This is a really basic question. Consider a good book on DB theory and SQL, it will greatly benefit you. See my recommendations: http://stackoverflow.com/questions/1046668/database-programming-concepts/1604980#1604980 — MaD70, Nov 08 '09 at 11:44

score 10 · Answer 1 · answered Nov 07 '09 at 15:51

10

Maybe just

SELECT *
FROM 
    Users
WHERE
    UserId <> @ThisUserId

Or using a Difference Union (The EXCEPT keyword in SQL Server, not sure about other RDBMS implementations)

SELECT *
FROM 
    Users

EXCEPT

SELECT *
FROM 
    Users
WHERE
    UserId = @ThisUserId

answered Nov 07 '09 at 15:51

Russ Cam

124,184
33
204
266

2

The INTERSECT keyword can also be extremely useful too, for finding results common to all resultsets – Russ Cam Nov 07 '09 at 16:09

score 5 · Answer 2 · answered Nov 07 '09 at 15:50

5

How about:

SELECT * FROM people WHERE person_id != $current_user_id

answered Nov 07 '09 at 15:50

AJ.

27,586
18
84
94

1

That should probably be person_id <> current_user_id – Andomar Nov 07 '09 at 15:51
Why? The RDBMS I use supports != operator. – AJ. Nov 07 '09 at 15:53
Interesting, just checked and SQL Server and MySQL support != – Andomar Nov 07 '09 at 16:07
+1 Hehe alright since I learned something new from your post :) – Andomar Nov 07 '09 at 16:14
`!=` is ANSI standard. It's been supported in Oracle since 9i; MySQL since at least 4.1 – OMG Ponies Nov 07 '09 at 16:39
@OMG Ponies: No, `<>` is ANSI standard. `!=` is supported by most RDBMS brands, but it's not specified in ANSI SQL. – Bill Karwin Nov 07 '09 at 18:01
2

See http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql/723426#723426 – Bill Karwin Nov 07 '09 at 18:02

alk · Answer 3 · 2018-10-23T17:45:27.560

Yes I know Im late with this one.

Anyhow as I stumpled over those comments here while looking for an answer to a question similare to the one ask here ("how to exlcude data from a query") I was a bit confused.

Confused because I knew the answer I was looking for was not only more simple, but even more elegant then the ones proposed here. I once knew the answer, but forgot it. I came here because I was too lazy to remember...

So I struggled hard to remember the easy solution and then it came back to my mind.

So assume you have the two tables "Email" and "UnwantedEmail" with both carring the one column "Address". The query to only get the wanted email addresses, those addresses which are in "Email" but not in "UnwantedEmail" could look like the following:

SELECT Email.Address FROM UnwantedEmail
RIGHT JOIN Email ON UnwantedEmail.Address=Email.Address
WHERE UnwantedEmail.Address Is Null;

score 2 · Answer 4 · edited Nov 07 '09 at 15:56

2

select * from Foo where UserName not in ('Rohan', 'Rohan's friend', .....)

Is this useful?

edited Nov 07 '09 at 15:56

Dominic Rodger

97,747
36
197
212

answered Nov 07 '09 at 15:53

Saar

8,286
5
30
32

score 0 · Answer 5 · answered Nov 07 '09 at 15:52

If you know what that user's unique ID is you could use something like this for example:

SELECT * FROM usertable WHERE id!='myuserid'

What I do with one of my authentication scripts is store the information for the person that is currently logged in in a variable so it would look like this in PHP:

SELECT * FROM usertable WHERE id!='check(id)'

Exclude Statement in SQL

5 Answers5

Linked