0

I have example query 

select id from clients where email like '%a%'
union 
select id from clients where email like '%b%'

which works fine, but i need to add limits like 

select id from clients where email like '%a%' limit 5
union 
select id from clients where email like '%b%' limit 5

but this breaks with 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union
select id from clients where email like '%b%' limit 5' at line 2

I made some tries and surprisingly 

select id from clients where email like '%a%' limit 5
union 
select id from clients where email like '%b%'

works fine

I'm using oracle mysql 8. Any ideas what I do wrong ?

Pavel Niedoba
  • 1,554
  • 2
  • 19
  • 36
  • When ORDER BY and/or LIMIT applied to separate queries is present then each separate query must be enclosed with brackets. [UNION clause](https://dev.mysql.com/doc/refman/8.0/en/union.html) – Akina Jun 15 '20 at 13:20
  • 1
    PS. LIMIT without ORDER BY gives non-deterministic result and hence makes no sense. – Akina Jun 15 '20 at 13:23
  • 1
    Your subject line says mysql, but you tagged both mysql and oracle. Which is it? – EdStevens Jun 15 '20 at 16:25
  • @Akina, well if you need some test data to populate pdf report, you don't really care if it's deterministic – Pavel Niedoba Jun 17 '20 at 08:56
  • It's oracle? But then you remove the 'oracle' tag and make the statement "I'm using oracle mysql 8" MySQL is not oracle. It is a totally different database product It just happens to be owned by the same company (Oracle Corp.) that owns the Oracle database. – EdStevens Jun 17 '20 at 12:02
  • I meant mysql owned by oracle and not mariadb, sorry for confusion – Pavel Niedoba Jun 18 '20 at 11:42

0 Answers0