37

What does <> mean in SQL language: Sample code is as follows

SELECT ordid,
       prodid,
       qty
FROM   item
WHERE  prodid IN (SELECT prodid
                  FROM   item
                  WHERE  ordid = 605)
       AND qty IN (SELECT qty
                   FROM   item
                   WHERE  ordid = 605)
       AND ordid <> 605;
Jay
  • 520
  • 2
  • 9
  • 23
  • Welcome! If you've an answer to your problem, click the outline tick next to the correct solution. There's no need to edit your post. – Jeremy McGee Oct 04 '11 at 09:59
  • 2
    @Jay - I deleted my answer as I had misread the query originally. The conditions aren't contradictory at all. Somewhat clearer with the reformatted query! – Martin Smith Oct 04 '11 at 10:06
  • No worries Martin, sorry about that. The answer we are given is "Display the order number, product number, and quantity of any item in which the product number and quantity match any product number and any quantity of an item in order 605." Although I am still little confused – Jay Oct 04 '11 at 10:07
  • @MartinSmith Also I was wondering if you have any resources that have all the conditions and operators that can help me revise? – Jay Oct 04 '11 at 10:09
  • @Jay - [They seem to be covered here](http://www.java2s.com/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperatorsinPLSQL.htm) – Martin Smith Oct 04 '11 at 10:13

8 Answers8

46

It means 'not equal to'. So you're filtering out records where ordid is 605. Overall you're looking for any records which have the same prodid and qty values as those assigned to ordid 605, but which are for a different order.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
11

Does not equal. The opposite of =, equivalent to !=.

Also, for everyone's info, this can return a non-zero number of rows. I see the OP has reformatted his question so it's a bit clearer, but as far as I can tell, this finds records where product ID is among those found in order #605, as is quantity, but it's not actually order #605. If order #605 contains 1 apple, 2 bananas and 3 crayons, #604 should match if it contains 2 apples (but not 3 dogs). It just won't match order #605. (And if ordid is unique, then it would find exact duplicates.)

brymck
  • 7,555
  • 28
  • 31
4

I'm surprised nobody mentioned the null special case. I think the meaning of <> is more something like

has a value which is not equal to

In this case it filters out items which have ordid 605 and items which have a null ordid.

It may be obvious in this context that ordid is never null, but it is never hurts to remember that null is not <> from 605 (or from anything).

bwt
  • 17,292
  • 1
  • 42
  • 60
4

It just means "different of", some languages uses !=, others (like SQL) <>

Arnaud F.
  • 8,252
  • 11
  • 53
  • 102
4

not equals. See here for a list of conditions

michael667
  • 3,241
  • 24
  • 32
0

It (<>) is a function that is used to compare values in database table.

!= (Not equal to) functions the same as the <> (Not equal to) comparison operator.

akhil
  • 1,649
  • 3
  • 19
  • 31
0

It means not equal to

Should I use != or <> for not equal in TSQL?

Have a look at the link. It has detailed explanation of what to use for what.

Community
  • 1
  • 1
Jay
  • 520
  • 2
  • 9
  • 23
-1

It means not equal to, this is a good method to exclude certain elements from your query. For example lets say you have an orders tables and then you have OrderStatusID column within that table.

You also have a status table where 

0 = OnHold, 
1 = Processing, 
2 = WaitingPayment, 
3 = Shipped, 
4 = Canceled.

You can run a query where 

Select * From [Orders] where OrderStatusID <> 4

this should give you all the orders except those that have been canceled! :D

Lostaunaum
  • 697
  • 1
  • 10
  • 31