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

"Is there a shorter alternative to my MySql query" which one i don't see a SQL query within this question. — Raymond Nijland, Apr 05 '18 at 15:14
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) — Valter Ekholm, Apr 06 '18 at 09:46
Hi. Please read & act on [mcve]. Note constraints allow otherwise unavailable options in writing/optimizing queries. Please clarify via editing your post, not via comments. Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Learn what full join on returns: inner join on rows plus unmatched rows extended by nulls. (Typically, left join on union right join on.) (Not supported by MySQL.) — philipxy, Apr 06 '18 at 20:43
What is your question? Do you seek a query? Or a better one? Better than what? "give" "employees without departments and departments with no employees" is not clear--how are they to be given? Please edit your post to be clear. — philipxy, Apr 06 '18 at 20:58
I made corrections. My comment: From the "green" answer it can be concluded that my question was actually two questions - both about (outer) joins in MySql and also about why to use Foreign Key constraint. Sorry if that vagueness is disturning. — Valter Ekholm, Apr 09 '18 at 10:37
If there is more than one other commenter and you want one of them to be notified of your comment who isn't the post owner then you need to write @user. Google 'stackexchange notifications'. — philipxy, Apr 12 '18 at 08:29

spencer7593 · Accepted Answer · 2018-04-09T20:36:09.040

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.

Thank you so much. I implemented your first example, with a "cost"-gain (from 19.2 to 17.4), and thereafter a redesigned the tables with FK-constraints, and then got further cost gain (from 17.4 to 15.2). Some code-typos I corrected though. — Valter Ekholm, Apr 09 '18 at 10:29

Is there a shorter alternative to my MySql query?

1 Answers1