SQL Server – Difference Between Joining One Table Twice and Using OR Operator

sql server

I have a table Orders that contains creatorId and editorId. My query is trying to compare a current user userId = creatorId OR userId = editorId. So I must join table 2 times. It seems like that:

select... 
from       Orders o 
inner join Employee e1 
on         o.creatorId = e1.userId
inner join Employee e2 
on         o.ediorId = e2.userId

–It returns 174 rows.

But I'm really incredulous when using OR operator. The query will be:

select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.creatorId 
           OR 
           e.userId = o.editorId

–It returns 181 rows.

The result has a small difference here. It means 2 query upper is not same. So I will try by another way, using UNION. The query may like:

select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.creatorId
UNION
select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.editorId

–It returns 181 rows.

And finally, I will check the difference (missing rows) by using EXCEPT:

select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.creatorId 
           OR 
           e.userId = o.editorId
EXCEPT
select... 
from       Orders o 
inner join Employee e1 
on         e1.userId = o.creatorId
inner join Employee e2 
on         e2.userId = o.editorId

–It returns 7 missing rows.

Single query (temporarily called as first single query):

select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.creatorId

return 174 rows same to:

select... 
from       Orders o 
inner join Employee e 
on         e.userId = o.editorId

My friend said that the second inner join is USELESS/MEANINGLESS for this case (the query that inner join one table 2 times is same as 1st single query). Though I have found that depending on several cases, sometimes they can join a table more than 2 times. But I'm really don't know to explain the difference between these upper queries. Please give me some advice, which query is right? Thanks so much.

Best Answer

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

Related Question