MySQL - how do I do an outer join where either side is optional - left and right outer join

Question

I need to join 2 MySQL tables that will return rows if Either side of the join exists, and only one row if both side exist.

Here is a a simple example:

CREATE TABLE `a` (
  `id` INT(11) ,
  `qty` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `b` (
  `id` INT(11) ,
  `price` DECIMAL(8,2)  DEFAULT NULL,
   PRIMARY KEY (`id`)
);

INSERT  INTO `a`(`id`,`qty`) VALUES (1,1),(3,0);
INSERT  INTO `b`(`id`,`price`) VALUES (1,'10.00'),(2,'20.00');

SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id;
returns 1 and 3

SELECT * FROM b 
LEFT OUTER JOIN a ON a.id=b.id;
returns 1 and 2


SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id
  UNION
SELECT * FROM b 
LEFT OUTER JOIN a ON a.id=b.id;
returns 1, 3, 1 , 2 

SELECT * FROM a 
LEFT OUTER JOIN b ON a.id=b.id
  UNION
SELECT * FROM b 
RIGHT OUTER JOIN a ON a.id=b.id;
returns 1, 3, 1

Is this possible in MySQL to get 1,2,3 or do I have to program around it?

So, are you just wanting to get the id from each table, if id exists in both return id, price and qty? — Rwd
– Rwd, Commented Dec 11, 2013 at 22:34

Community · Accepted Answer · 2017-05-23 12:29:16Z

3

What you need is a FULL OUTER JOIN, which, unfortunately, is not implemented in MySQL. You can achieve the same with a union, however. See this answer for how to do this.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Dec 11, 2013 at 22:37

willy

1,50011 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

MySQL - how do I do an outer join where either side is optional - left and right outer join

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related