Difference between joining one table twice times and operator OR
I've set up the below sample: (There is a link at the end of the answer)




create table orders (orderId int primary key, creatorId int, editorId int);
create table users  (userId int primary key, name varchar(10));
insert into users values (1, 'A'),(2, 'B'),(3, 'C'),(4,'D');
insert into orders values
(1, 1, 2), (2, 1, 3), (3, null, 4), (4, 2, null);
GO



First let me have a look at which rows are returned using a single JOIN for both conditions.




select     *
from       orders
inner join users
on         creatorId = userId;
GO



orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A   
      2 |         1 |        3 |      1 | A   
      4 |         2 |     null |      2 | B   






select     *
from       orders
inner join users
on         editorId = userId;
GO



orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      2 | B   
      2 |         1 |        3 |      3 | C   
      3 |      null |        4 |      4 | D   



As you can see orders 1,2,4 match firts condition: creatorId = userId and orders 1,2,3 match the second one: editorId = userId

This should give you one idea about what will happen using OR and joining twice table users with editorId and creatorId.

Ok, first we will try using OR:




select     *
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO



orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A   
      2 |         1 |        3 |      1 | A   
      1 |         1 |        2 |      2 | B   
      4 |         2 |     null |      2 | B   
      2 |         1 |        3 |      3 | C   
      3 |      null |        4 |      4 | D   



That is, it returns orders 1,2,3 plus orders 1,2,4, all the records returned by first single query plus all records returned by the second one.

But what happens when you join twice users table:




select     *
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO



orderId | creatorId | editorId | userId | name | userId | name
------: | --------: | -------: | -----: | :--- | -----: | :---
      1 |         1 |        2 |      2 | B    |      1 | A   
      2 |         1 |        3 |      3 | C    |      1 | A   



Hey, only orders 1,2 are there. If you remember the single queries, which returned rows are equals in both results? 1 and 2, and that's what you asked for: Give me the records where creatorId match a user AND editorId match a user too.

Humm, ok, what happens if I use AND instead of OR:




select     *
from       orders
inner join users
on         editorId = userId
AND        creatorId = userId;
GO



orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---



No rows are returned, ok what have we asked here?

Give me the rows where one editorId = creatorId. 




select     *
from       orders
inner join users
on         creatorId = userId
UNION
select     *
from       orders
inner join users
on         editorId = userId;
GO

  
  Ok, now with UNION:


orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A   
      1 |         1 |        2 |      2 | B   
      2 |         1 |        3 |      1 | A   
      2 |         1 |        3 |      3 | C   
      3 |      null |        4 |      4 | D   
      4 |         2 |     null |      2 | B   



What was the question: Give all records that match first condition PLUS all records that match second condition, that is: orders 1,2,3 + orders 1,2,4

Using EXCEPT condition:




select     orderId, creatorId, editorId, userId, name
from       orders
inner join users
on         editorId = userId
or         creatorId = userId
EXCEPT
select     orderId, creatorId, editorId, u1.userId, u1.name
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO



orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A   
      2 |         1 |        3 |      1 | A   
      4 |         2 |     null |      2 | B   
      3 |      null |        4 |      4 | D   



Question: Give all records returned by the query using OR: Orders: 1,2,1,4,2,3 except those records returned joining twice users table, Orders: 1, 2

Well now the final point on your question:


  which query is right?


I don't know. It depends on what is the desired result.

Please, tell us what are you trying to accomplish with your query.

Ok, your last comment clarify your question:


  My target is getting all data that userId=creatorId or userId=editorId without duplicating any orders


The easiest way is to use DISTINCT:




select     distinct orderId, editorId, creatorId
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO



orderId | editorId | creatorId
------: | -------: | --------:
      1 |        2 |         1
      2 |        3 |         1
      3 |        4 |      null
      4 |     null |         2



Or you can use a LEFT JOIN if you want to add information of users table.




select     orderId, creatorId, u2.Name as creatorName, editorId, u1.Name as editorName
from       orders
left  join users u1
on         editorId = u1.userId
left  join users u2
on         creatorId = u2.userId;
GO



orderId | creatorId | creatorName | editorId | editorName
------: | --------: | :---------- | -------: | :---------
      1 |         1 | A           |        2 | B         
      2 |         1 | A           |        3 | C         
      3 |      null | null        |        4 | D         
      4 |         2 | B           |     null | null      



dbfiddle here