CODEWITHSUNDEEP
sqlsql-serverdatabaseazureu-sql
Harish K
Harish K

Reputation: 11

Is there any alternative way of achieving below logic in sql?

I have two tables -> tb1 and tb2. I am performing left join operation on these tables using ID column and also i have one more condition such as one column is not equal to other column . Below is sample code

select * from tb1 LEFT JOIN tb2 ON tb1.id=tb2.id AND tb1.pid!=tb2.pid;

I am able to get results from above query.

But i need to know is there any alternate ways to get same result using sql.?

Upvotes: 0

Views: 383

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416049

The actually SQL standard uses <> instead of !=. 

select * from tb1 LEFT JOIN tb2 ON tb1.id=tb2.id AND tb1.pid<>tb2.pid;

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

It seems to you not equal not working because of your join and join condition. if we create two tables and create like your query 

create table t1(id int,pid int);
create table t2 (id int,pid int );
insert into t1 values(1,2),(2,3),(3,4);
insert into t2 values(1,2),(2,3),(3,4);

select t1.* from t1 left join
t2 on t1.id=t2.id and 
t1.pid!=t2.pid
order by t1.id 

id  pid
1   2
2   3
3   4

It returns all the values of 1st table, because LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

But if you put inner join in the same it will not return any row. so i think problem is not in the "not equal operator"

Upvotes: 0

Related Questions