CODEWITHSUNDEEP
azure-data-lakeu-sql
Harish K
Harish K

Reputation: 11

How to use Not equal to condition in U-SQL?

I am trying to write a U-SQL query to replicate the same logic which SQL query does as below.

SELECT * 
FROM tb1 
LEFT JOIN tb2 
ON tb1.id=tb2.id AND tb1.pid!=tb2.pid;

Since U-SQL doesn't support Not equal to in join condition (tb1.pid!=tb2.pid).

Is there a way to achieve this logic in USQL?

Upvotes: 1

Views: 351

Answers (1)

Binarus
Binarus

Reputation: 4405

First, please note that using any comparison operator other than equality in a JOIN clause usually degrades the performance of the query, and I personally would consider it bad style. After all, it is a JOIN clause and not a WHERE clause, meaning that it should tell the DBMS which rows to "link" together (and not which rows to not "link").

This is true even if the DBMS allows the use of all sorts of comparison operators in the JOIN clause.

Having said this, just move the != comparison into a WHERE clause (where it belongs to anyway due to the reasons mentioned above):

SELECT * 
FROM tb1 
LEFT JOIN tb2 
ON tb1.id=tb2.id
WHERE tb1.pid!=tb2.pid;

This will produce exactly the same results as your original query.

Upvotes: 1

Related Questions