Joining tables/records that might not exist sql

Question

I have this query:

SELECT a.total
     , b.total
     , (a.total - b.total) as dv
     , a.customer_name
     , a.report_date
     , b.report_date 
  FROM topsourcesfcy a 
 CROSS 
  JOIN topsourcesfcy b 
 WHERE a.customer_name = b.customer_name 
   AND a.customer_name = 'SPECIALTY DRILLING FLUIDS LIMITED'
   AND a.report_date   = '2016-2-10' 
   AND b.report_date   = '2016-2-9' 
   AND a.report_date!  = b.report_date

but the problem is sometimes the record might exist in a and not in b or in b and not in a, which will cause the query to execute to false but I still want to select it if it exists in only one or both. Thanks.

@Strawberry mysql does not support full outer join, you need union to simulate it. — Shadow, Feb 12 '16 at 14:53
@Shadow I'm well aware of that thanks - as a simple search using those keywords would reveal. — Strawberry, Feb 12 '16 at 14:56

score 0 · Answer 1 · answered Feb 12 '16 at 14:55

Read more thorough answers here: Full Outer Join in MySQL

What you want is a full outer join, but mysql doesn't support it.

Instead you can use a combo of two outer joins (left and right) and a union, the following will also remove dupes:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

Joining tables/records that might not exist sql

1 Answers1