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

Jimko1 61 Reputation points
2021-03-08T15:58:15.547+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. Mattias Asplund 236 Reputation points
    2021-03-08T16:04:57.38+00:00

    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)


2 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,181 Reputation points
    2021-03-09T02:17:20.66+00:00

    Hi @Jimko1 ,

    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 person found this answer helpful.

  2. Joe Celko 16 Reputation points
    2021-03-08T21:10:20.803+00:00

    > 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),
    ..);