SQL: Join on two tables where on column is null

Question

I am using mysql database. I have two tables bugs and profiles. Bugs table has two columns (assigned_to, qa_contact) pointing to profiles by a many to one relationship. These are the simplified version of my queries.

Firstly, I was trying to do this but it returns duplicate rows where qa_contact is null in the bugs table

select 
  bug_id, 
  desc, 
  dev.assigned_to, 
  qa.qa_contact 
from 
 bugs, 
 profiles as dev, 
 profiles as qa
where 
  assigned_to = dev.userid 
  and (qa_contact = qa.userid or qa_contact is null)

Secondly, my new approach is:

select bug_id, desc, dev.assigned_to, qa.qa_contact 
from 
 bugs, 
 profiles as dev, 
 profiles as qa
where 
  assigned_to = dev.userid 
  and qa_contact = qa.userid

 UNION

select bug_id, desc, dev.assigned_to, null 
from 
 bugs, 
 profiles as dev, 
 profiles as qa
where 
  assigned_to = dev.userid 
  and qa_contact is null

But in the second approach it excludes the result where qa_contact is null. Can anyone suggest a efficient way of doing this because I am dealing with records in order of millions and would like to add more filters on the resultset.

please, use ANSI queries and add backticked aliases to ALL your tables and columns. — Sebas
– Sebas, Commented Nov 9, 2012 at 19:08

lc. · Accepted Answer · 2012-11-09 19:09:30Z

7

This is what LEFT JOINs are for:

SELECT bug_id, `desc`, dev.assigned_to, qa.qa_contact 
FROM bugs
INNER JOIN profiles as dev ON bugs.assigned_to = dev.userid
LEFT OUTER JOIN profiles as qa ON bugs.qa_contact = qa.userid

answered Nov 9, 2012 at 19:09

lc.

117k21 gold badges164 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Raunak Agarwal Over a year ago

Thanks. Just a quick question left joins are more efficient than Union operations right?

nawfal Over a year ago

@RaunakAgarwal with the same amount of data, yes but how does that matter when they both do two different things?

Raunak Agarwal Over a year ago

@nawfal: I am not sure how union operations work since, I was trying to union the results of the different set on multiple column.Would mysql check records for each set row by row for each column?

nawfal Over a year ago

@RaunakAgarwal I do not understand what you mean. If its a union query, the two separate queries are run and second result set is concatenated to first result set. Of course optimizations can play (for example when LIMIT is set, MySQL wont select the entire rows).

Raunak Agarwal Over a year ago

I am asking for the UNION operator not for the UNION ALL. You are missing the point, UNION operator only selects distinct results and UNION ALL concatenates the result.

|

Taryn · Accepted Answer · 2012-11-09 19:09:49Z

2

I think you want to use a LEFT JOIN:

select bug_id, desc, dev.assigned_to, qa.qa_contact 
from bugs b
left join profiles dev
  b.assigned_to dev.userid
left join profiles qa
  on b.qa_contact = qa.userid

If you need help learning JOIN syntax, then here is a great visual explanation of joins

A LEFT JOIN will return data from the bugs table even if the id does not exist in the profiles table.

answered Nov 9, 2012 at 19:09

Taryn

249k57 gold badges375 silver badges409 bronze badges

2 Comments

Raunak Agarwal Over a year ago

Thanks I just wasn't thinking right. I totally forgot about joins not a sql person. Would you suggest a join or Union operation what is more efficient?

Taryn Over a year ago

@RaunakAgarwal well you were using a UNION operator which also removes any duplicate records, if you have millions of records you will definitely have performance issues. For this operation I would use a JOIN

Collectives™ on Stack Overflow

SQL: Join on two tables where on column is null

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related