What's this symbol (<>) doing to this select statement?

Question

The code below retrieved 74,700 rows from the database.

select * from Orders O
      inner join customers C
     on O.CustomerID <> c.CustomerID

The same code with = retrieves 830 records.

 select * from Orders O
      inner join customers C
     on O.CustomerID = c.CustomerID

What's this not equal to symbol doing to my search query? The same difference is there in outer join too.

Thank you.

What part is unclear? It will join rows where customer id does **not** match — James Z, Feb 26 '17 at 15:57
You got 830 orders and 91 customers (assuming each customer ordered at least once). The 1st join is an equi-join, returning all orders. The 2nd join is quite stupid, it joins the order of a customer with all other customers, but not the actual one. The result is like a Cross-Join between both tables (91*830=75530 rows) minus the 1st result. — dnoeth, Feb 26 '17 at 16:06
See my answer, which explains conceptually about this ON clause, as i think relationally you need to understand what a Query operator does before the syntax differences make sense. — clifton_h, Feb 27 '17 at 08:49

score 3 · Answer 1 · answered Feb 26 '17 at 16:04

3

<> is the "not-equals" operator in SQL.

The query is getting all pairs or orders and customers where the customerId columns are different. What you really want is probably orders that don't have a valid customer id:

select o.*
from orders o left join
     customers c
     on o.CustomerID = c.CustomerID
where c.CustomerId is null;

(Actually, this seems unlikely if you have a proper foreign key relationship set up.)

Or more likely customers that don't have an order:

select c.*
from customers c left join
     orders o 
     on o.CustomerID = c.CustomerID
where o.CustomerId is null;

answered Feb 26 '17 at 16:04

Gordon Linoff

1,242,037
58
646
786

right join customers C on O.CustomerID = c.CustomerID.............................. retrieves 832 records – Bodhi Feb 26 '17 at 16:10
also my left outer join retrieves 830 records – Bodhi Feb 26 '17 at 16:12
what makes this difference in two . – Bodhi Feb 26 '17 at 16:13
3

@Bodhi Please stop asking additional questions in comments. Post a new question on SO instead. – Frank Schmitt Feb 26 '17 at 16:20
@Bodhi . . . This answer doesn't have a `right join`. There is no reason to expect that a `right join` and `left join` would retrieve the same number of rows. – Gordon Linoff Feb 26 '17 at 20:12

score 0 · Answer 2 · answered Feb 26 '17 at 16:07

0

<> symbol means not equal to ie) O.CustomerID not equal to c.CustomerID or you can use != which also means not equal to in sql

answered Feb 26 '17 at 16:07

Prabha Rajan

70
1
4

score 0 · Answer 3 · answered Feb 26 '17 at 16:18

The not equal <> operator returns true when the values are NOT EQUAL

The code on O.CustomerID <> c.CustomerID seems to join every row of the orders table with every row of the customers table that is not equal to it. Here is an example in the SQL fiddle.

http://sqlfiddle.com/#!9/e05f92/2/0

As you can see, in the top select (one where an = sign is used), it only selects the rows where the order customerID is equal to the Customer customerID

In the bottom select (where the <> is used) it joins every customer row, with every possible order row which is not equal, which is why you get so many results for the <> query.

clifton_h · Accepted Answer · 2017-02-27T06:43:59.770

The ON operator

Logically, every SQL query is executed in the following order:

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

You can read about this further from the official documentation from MSDN. SELECT (Transact-SQL)

This means the on predicate relates to the cardinal matches between tables, while the WHERE clause filters the results.

Cardinal means 1:N, or the number of matches to a side. In your example, ON A.CUSTOMER_ID = B.CUSTOMER_ID will return a row for every matching set from the source table

LEFT and RIGHT refer to which side is the source table. By default, the left is considered the source table.

So if table A has 3 rows where ID = 3, then even if table B has only one ID of 3, you will return 3 rows; each row in Table A is treated separately.

A good join only uses the number of columns required to return a unique join, so that unwanted repeating values are not returned. Even if you meant to use a CROSS JOIN, you still need to make sure to use unique matching sets for your purpose.

Relationally, what does the joins mean?

This is the real question: what do the tables represent and how do they answer a question? Relational means value, information, a question or query answered.

When you know what the batch or proc does or what its purpose is for the script(s), identifying silly queries becomes easier.

CONCLUSION

ON ID = ID - selects matching rows.
ON ID <> ID - returns every nonmatching row for every row in the source table. Essentially a cross join minus the actual join rows.

Good practice is to use the ON to identify unique rows that match and the WHERE clause to filter this result on the side of the source table.

What's this symbol (<>) doing to this select statement?

4 Answers4