1

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.

7
  • Do you have Order rows where either the CreatorId or EditorId do not have a corresponding row on the Employee table? Your first query must find a match for both CreatorId AND EditorId on the Employee table while your second query is satisfied by finding a match for either CreatorId OR Editorid Apr 17, 2017 at 9:24
  • @ScottHodgin, I need to compare userId = creatorId OR userId = editorId (sorry for my bad English). Yes, the query select * from Orders where creatorId != editorId returns 7 rows same to EXCEPT statement. I will check more, thanks for your suggest so much.
    – mmo2112
    Apr 17, 2017 at 9:48
  • Please add your table schema, foreign keys included.
    – McNets
    Apr 17, 2017 at 10:34
  • @McNets, I can't upload the tables. It's personal data and saving as native language. I have tried to simply the query, it will be: <br> 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.
    – mmo2112
    Apr 17, 2017 at 10:55
  • 1
    We don't need the data only the schema, are there null editorId or creatorId's?
    – McNets
    Apr 17, 2017 at 10:57

1 Answer 1

3

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

1
  • Thanks so much, I have understood a bit. I recognize in this case I must change inner join to left join. After reading your code carefully, I think the right query is left join table users 2 times. Because the operator OR give us redundant data (we have 4 orders data rows, the maximum return must <= 4). My target is getting all data that userId=creatorId or userId=editorId without duplicating any orders. So, the right query is left join users 2 times, isn't it? Many thanks for your help.
    – mmo2112
    Apr 17, 2017 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.