CODEWITHSUNDEEP
sqlsql-serverselectwhere-clause
Bodhi
Bodhi

Reputation: 548

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

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.

Upvotes: 1

Views: 130

Answers (4)

clifton_h
clifton_h

Reputation: 1298

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.

Upvotes: 0

Andrei T
Andrei T

Reputation: 173

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.

Upvotes: 0

Prabha Rajan
Prabha Rajan

Reputation: 68

<> 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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

<> 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;

Upvotes: 3

Related Questions