question

Jimko1-7792 avatar image
0 Votes"
Jimko1-7792 asked ·

How to return results where (row by row) column 1 data must be different from column 2 in SQL server

Hi everyone

I need to work out how to return results on two columns where (row by row) column 1 must be different from column 2 and both columns also need to contain distinct values (in SQL server)

  • the main essence of the question is how to ensure results are different from column 1 compared with column 2 on a row by row basis..

thank you!

sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MattiasAsplund-3483 avatar image
0 Votes"
MattiasAsplund-3483 answered ·

I interpret "distinct values" as having values not equal to NULL.

In that case, my suggestion would be:

 SELECT * FROM table WHERE col1 != col2

(table, col1 and col2 needs to be replaced with whatever names you have for your table and columns)

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

great , thank you

0 Votes 0 ·
JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 answered ·

the main essence of the question is how to ensure results are different from column 1 compared with column 2 on a row by row basis.<<

Is there some reason you didn't post any DDL at all? We don't even know the name of this table, thanks to your bad manners. Here's a skeleton that will assure each of the columns foo and bar are always different.

CREATE TABLE Foobar
( .. PRIMARY KEY,
foo INTEGER NOT NULL,
bar INTEGER NOT NULL,
CHECK (foo <>bar),
..);

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

im sorry my question did not meet your expectations, im a new SQL user and this is my first post.
Im sure you were learning once. Im not sure I need to post any code if thats what you referring to, since the question I feel is pretty clear to most people hence the polite and useful responses I have recieved.
If there are some best practices i should be following pls let me know in a constructive way.

You stating I have bad manners does not help me learn it just affects my confidence and makes me not want to post again. Luckily others here have been more supportive.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ·

Hi @Jimko1-7792,

Welcome to Microsoft Q&A!

Glad that you already got your accepted answer!

We could use both SQL Not Equal operators <> and != to do inequality test between two expressions. Both operators give the same output.

The only difference is that ‘<>’ is in line with the ISO standard while ‘!=’ does not follow ISO standard. You should use <> operator as it follows the ISO standard.

So we could also use below:

 SELECT * FROM table WHERE col1 <> col2

If you also have NULL in any column, you could consider to use ISNULL function.

 SELECT * FROM table WHERE isnull(col1,'')<>isnull(col2,'')

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks so much Melissa, super helpful

0 Votes 0 ·