CODEWITHSUNDEEP
mysql
sdfor
sdfor

Reputation: 6438

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

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?

Upvotes: 2

Views: 436

Answers (1)

willy
willy

Reputation: 1490

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.

Upvotes: 3

Related Questions