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
select * from Orders where creatorId != editorIdreturns 7 rows same to EXCEPT statement. I will check more, thanks for your suggest so much.select * from Orders o left join Employee e1 on e1.userId=o.creatorId inner join Employee e2 on e2.userId = o.editorId where o.pk_seq =5613541<br/> --Return 1 row. Table Orders has pk_seq is PK.select * from Orders o left join Employee e on e.userId=o.creatorId or e.userId = o.editorId where o.pk_seq =5613541--Return 2 row. I choose o.pk_seq =5613541 because it in the list of returning 7 rows upper EXCEPT statement.