Is there a shorter alternative to my MySql query?

Question

I'm a student of Java and do SQL too. In a lesson we were presented with an example database sketch, and a query that a replicate in this question.

I have made an example with MySql and it has three tables,

CREATE TABLE `employed` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `employees_departments` (
`employed_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`employed_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

employed was filled with

(1 'Karl'), (2 'Bengt'), (3 'Adam'), (4 'Stefan')

department was filled with

(4, 'HR'), (5, 'Sälj'), (6, 'New departm')

employees_departments was filled with

1 4
2 5
3 4

So "Stefan" has no department, and "New departm" has no employed.

I wanted a query that would give the employees with all their departments, and employees without departments and departments with no employees. I found on solution like this:

select A.name, C.name from employed A
left join employees_departments B on (A.id=B.employed_id)
left join department C on (B.department_id = C.id)
union
select C.name, A.name from department A
left join employees_departments B on (A.id=B.department_id)
left join employed C on (B.employed_id = C.id)

Would be nice if there was a short query to make it...

Also, I made this without foreign key constraints, since I want to do it as simple as possible for this example.

Greetings

Answer 1

MySQL doesn't support a FULL OUTER join operation.

We can emulate that by combining two sets... the result of an OUTER JOIN and the result from an anti-JOIN.

(
  SELECT ee.name        AS employed_name
       , dd.name        AS department_name 
    FROM employed ee
    LEFT
    JOIN employees_departments ed
      ON ed.employed_id = ee.id
    LEFT
    JOIN department dd
      ON dd.id = ed.department_id
)
UNION ALL 
(
  SELECT nn.name        AS employed_name
       , nd.name        AS department_name
    FROM department nd
    LEFT
    JOIN employees_departments ne
      ON ne.deparment_id = nd.id
    LEFT
    JOIN employeed nn
      ON nn.id = nd.employee_id
   WHERE nn.id IS NULL
)

The first SELECT returns all employed name, along with matching department name, including employed that have no department.

The second SELECT returns just department name that have no matching rows in employed.

The results from the two SELECT are combined/concatenated using a UNION ALL set operator. (The UNION ALL operation avoids a potentially expensive "Using filesort" operation that would be forced with the UNION set operator.

This is the shortest query pattern to return these rows.

---

We could make the SQL a little shorter. For example, if we have a foreign key relationships between employeed_department and employed (no indication in the original post that such a relationship is enforced, so we don't assume that there is one)... but if that is enforced, then we could omit the employed table from the second SELECT

UNION ALL
(
  SELECT NULL           AS employed_name
       , nd.name        AS department_name
    FROM department nd
    LEFT
    JOIN employees_departments ne
      ON ne.deparment_id = nd.id
   WHERE ne.department_id IS NULL
)

With suitable indexes available, this is going to give us the most efficient access plan.

Is there shorter SQL that will return an equivalent result? If there is, it's likely not going to perform as efficiently as the above.

---