SQL "with" clause - Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS

Question

I have the following query which works fine:

select a.dataId, a.my_no,  b.my_attribute  from myDB.table_a a left join myDB.table_b b 
on a.my_no = b.my_no order by dataId

However, if I include the with clause like below:

with my_table as (
    select a.dataId, a.my_no,  b.my_attribute  from myDB.table_a a left join myDB.table_b b 
    on a.my_no = b.my_no order by dataId
)

select * from my_table

I got the following error:

Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

This is confusing ... shouldn't these two queries be identical? What did I do wrong here? (I am using MariaDB server) Thanks!

Answer 1

Before a WITH clause, you should specify the database you're using with

USE db_name;

That should solve the problem.

Answer 2

just do:

select * from myDB.my_table

works as well.