Using Inner Join the same result is returned multiple times

Question

I have used the following query:

OrderMaster table:

OrderId
UserId - F.K

Users table:

Id - P.K
Name
Email
Mobilenumber

Query:

SELECT 
    CAST(o.Id as varchar(50)) as ID, 
    CAST(o.Name as varchar(50)) as Name,
    CAST(o.ContactNumber as varchar(50)) as Mobilenumber, 
    o.Email 
FROM 
    ordermaster as t1 
INNER JOIN
    Users as o ON t1.UserId<> o.Id

The results look like this:

Id  Name   Mobilenumber  Email
-------------------------------------------
1   xxxx   252548878     [email protected]
2   yyyy   422557879     [email protected]
1   xxxx   252548878     [email protected]
2   yyyy   422557879     [email protected]

Why is the same result returned multiple times? Please clarify me.

Answer 1

Since your join condition is <> and not =, each record in User join with all the records in Ordermaster that have a different id, giving the same result multiple times. If you want to get all the Users which are not in Ordermaster then you can do:

SELECT ...
FROM Users u
WHERE NOT EXISTS (SELECT * FROM ordermaster WHERE u.Id = Id)