SQL UNION failed

Question

This is my query:

select dept_name, count(*)  as student_number 
from Student s join Department d
on s.dept_id=d.dept_id
group by dept_name 
order by student_number desc, dept_name asc
UNION 
select d.dept_name, 0 as student_number
from Department d left join Student s
on s.dept_id=d.dept_id
where student_id  is NULL

Error:

You have an error in your SQL syntax; check the manual that corespond to your mysql server version for the right syntax to use near 'UNION' select d.dept_name, 0 as student_number from Department d left join Student s

I have 2 tables the first:

Column Name	Type
student_id	int
student_name	varchar
gender	varchar
dept_id	int

the second

Column Name	Type
dept_id	int
dept_name	varchar

Does this answer your question? [How to order by with union in SQL?](https://stackoverflow.com/questions/4715820/how-to-order-by-with-union-in-sql) — derpirscher, Feb 06 '22 at 08:19
If you need to sort the rowset of separate subquery then you must enclose this subquery into the parenthesis. ```(SELECT ... ORDER BY ...) UNION SELECT ...```. But this makes no sense - UNION performs its own sorting which will kill inner sorting result. — Akina, Feb 06 '22 at 09:58

score 2 · Answer 1 · answered Feb 06 '22 at 08:16

2

The problem with your query is the ORDER BY clause, which is not allowed in the first of the unioned queries.
It should be placed last to the final result of the union.

But, you don't need UNION.
You can do it with a LEFT join of Department to Student:

SELECT d.dept_name, count(s.student_id) AS student_number 
FROM Department d LEFT JOIN Student s
ON s.dept_id = d.dept_id
GROUP BY d.dept_name 
ORDER BY student_number DESC, dept_name ASC;

answered Feb 06 '22 at 08:16

forpas

160,666
10
38
76

Thank you it worked. My first try was with the UNION after many attempt i move with the left join solution.By the way the query UNION runtime was 60% faster than the left join. – David Belhamou Feb 06 '22 at 08:38
@DavidBelhamou if UNION is faster then try UNION ALL instead. It performs even better. – forpas Feb 06 '22 at 08:43
*The problem with your query is the ORDER BY clause, which is not allowed in the first of the unioned queries.* This is not correct. [ORDER BY and LIMIT in Unions](https://dev.mysql.com/doc/refman/8.0/en/union.html#union-order-by-limit) – Akina Feb 06 '22 at 09:56
@Akina if this is not correct then why if the ORDER BY clause is removed the query runs fine? Unless you mean that the 1st query should be enclosed inside parentheses which is a different case and also useless for the current requirement. – forpas Feb 06 '22 at 09:59
Link added. It explains correct syntax completely. Adding ORDER BY to the 1st subquery obviously tells that OP needs to sort the output of this subquery, is it? If so then syntax error - parenthesis needed (not ORDER BY not allowed). – Akina Feb 06 '22 at 10:00
@Akina I saw the link and I know the syntax and as I said: *... the ORDER BY clause, which is not allowed in the first of the unioned queries*. This is what the documentation mentions: *To apply an ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses*. – forpas Feb 06 '22 at 10:02
"Not allowed" <> "Allowed only with parenthesis". – Akina Feb 06 '22 at 10:05
Anycase - UNION DISTINCT makes this ORDER BY meaningless. – Akina Feb 06 '22 at 10:06
@Akina *Allowed only with parenthesis* is a different query. The ORDER BY clause in unioned queries is allowed only at the end of the statement. I already mentioned in a previous comment that the ORDER BY clause is useless inside parenthesized queries. – forpas Feb 06 '22 at 10:10
Well, with any syntax error, we can say that the corrected version is a completely different query... – Akina Feb 06 '22 at 10:40
@Akina for the question's syntax error we can say that the ORDER BY causes the error because it is placed where it is not allowed. – forpas Feb 06 '22 at 10:46

SQL UNION failed

1 Answers1