CODEWITHSUNDEEP
mysqlsqlunion
MetalicSt33l
MetalicSt33l

Reputation: 45

Count of full outer join in mySQL

I'm trying to display the total number of rows in a full outer join table. I have the following code, but mysql says there is an error with duplicate columns. The 2 tables, actors and directors, have the same columns as they are supposed to provide similar information in their respective categories.

SELECT COUNT(*) FROM 
(SELECT * FROM directors LEFT OUTER JOIN actors
ON directors.name = actors.name
UNION
SELECT * FROM directors RIGHT OUTER JOIN actors
ON directors.name = actors.name) AS table1;

What can be done to fix the code so it will run properly? FYI, the code from within the parenthesis runs fine. The problem only arises once I put in the SELECT COUNT(*) clause.

Upvotes: 1

Views: 4092

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I'm not sure what you are getting at with the full join. But the best way to implement it in MySQL uses two left joins and a union:

select count(*)
from ((select name from directors) union -- on purpose
      (select name from actors)
     ) da left join
     directors d
     on da.name = d.name left join
     actors a
     on da.name = a.name;

If I had to guess, though, you just want the number of distinct names between the two tables. If so:

select count(*)
from ((select name from directors) union -- on purpose
      (select name from actors)
     ) da

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15951

It might be better to change the right join portion non-redundant, and just add separate counts.

Generic version:

SELECT (SELECT COUNT(*) FROM A LEFT JOIN B ON A.x = B.x) 
+ (SELECT COUNT(*) FROM B LEFT JOIN A ON B.x = A.x WHERE A.x IS NULL)
AS outerJoinSize
;

Note: I changed the RIGHT JOIN to a LEFT JOIN and swapped the tables around; in my experience, RIGHT JOIN just tends to make queries a little harder to read (especially when multiple joins are involved).

An completely different alternative...

SELECT 
( SELECT SUM(dc1.c * IFNULL(ac1.c, 1)) AS jc
  FROM (SELECT name, COUNT(*) AS c FROM directors GROUP BY name) AS dc1 
  LEFT JOIN (SELECT name, COUNT(*) AS c FROM actors GROUP BY name) AS ac1
     ON dc1.name = ac1.name)
+ (SELECT SUM(IF(dc2.name IS NULL, ac2.c, 0)) AS jc
   FROM (SELECT name, COUNT(*) AS c FROM actors GROUP BY name) AS ac2
   LEFT JOIN (SELECT name, COUNT(*) AS c FROM directors GROUP BY name) AS dc2 
      ON ac2.name = dc2.name)

...this one figures out how many matches based on the joining field (3 instances of "Bob" in directors and 2 in actors means 6 join results for that name).

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

Becuase there are two name columns one is from directors table, another is from actors table, and you select * that will let DB engine confuse which name did you want to get.

if you only want to count total number you can try this.

SELECT COUNT(*) FROM 
(
    SELECT directors.name FROM directors LEFT OUTER JOIN actors
        ON directors.name = actors.name
    UNION
    SELECT directors.name FROM directors RIGHT OUTER JOIN actors
        ON directors.name = actors.name
) table1;

NOTE

I would suggest use select clear the columns and avoid using select *

Upvotes: 1

Related Questions