CODEWITHSUNDEEP
mysqlsql
jskidd3
jskidd3

Reputation: 4783

MySQL Union - Select data from first table in union first

SELECT service.*
FROM star_service
INNER JOIN service ON service.code = star_service.service

UNION

SELECT service.*
FROM service

How can I modify the above query so that the results from the first table are shown first followed by the second query in the union?

Upvotes: 1

Views: 5270

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would skip the union altogether. If you want everything from service with the ones in star_service first, then just use left join and order by:

select s.*
from service s left join
     star_service ss
     on s.code = ss.service
order by (ss.service is not null) desc;

EDIT:

If there are duplicates in star_service, then you are better off using exists:

select s.*, (exists (select 1 from start_service ss where s.code = ss.service) ) as in_ss
from service s
order by (is_ss is not null) desc;

These versions (with the proper indexes) should perform much, much better than your original version or anything with a union.

Upvotes: 2

koushik veldanda
koushik veldanda

Reputation: 1107

better to go with UNION ALL instead of UNION because

union gives sorted output.

union all gives unsorted output 

SELECT service.*
FROM star_service
INNER JOIN service ON service.code = star_service.service

UNION ALL

SELECT service.*
FROM service

Upvotes: 0

Bohemian
Bohemian

Reputation: 425348

Use UNION ALL, which preserves selected order of the results of the two queries in the order coded (and preserves duplicates).

Just add ALL:

SELECT service.*
FROM star_service
INNER JOIN service ON service.code = star_service.service

UNION ALL

SELECT service.*
FROM service

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18767

Add an additional column to the result set, then use that column to for ordering.

Like this:

SELECT service.*, 1 as OBCol
FROM star_service
INNER JOIN service ON service.code = star_service.service

UNION

SELECT service.*, 2 as OBCol
FROM service

ORDER BY OBCol

Upvotes: 6

Related Questions