CODEWITHSUNDEEP
mysqlsqljoininner-join
Michael
Michael

Reputation: 14218

MySQL full inner join

I have 

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null;

that does the equivalent of 

SELECT clientReport.id
WHERE clientReport.rowNumber NOT EXISTS (
SELECT clientReport.rowNumber FROM report02, clientReport
WHERE report02.id=clientReport.id);

I need, presumably, a full inner join to also get mismatches in report02, not just clientReport. How do I write the join to do this?

Upvotes: 1

Views: 1685

Answers (1)

JStead
JStead

Reputation: 1730

The below should work.

SELECT clientReport.id,report02.id
FROM clientReport 
FULL OUTER JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null
OR clientReport.id is null;

It should but it doesn't (as MySQL does not currently support FULL OUTER JOIN.)

This is more likely to work:

( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  LEFT  OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE report02.id IS NULL
)
UNION
( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  RIGHT OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE clientReport.id is null
)

Upvotes: 1

Related Questions