joined query output to csv file in mysql workbench?

Question

I know you can export a table from mysql workbench easily however how do I output joined table to csv file?

I've checked How to export table data in MySql Workbench to csv? and following its suggestions tried

select
  *
  INTO OUTFILE '/tmp/products.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
from table_1 t_1
left join table_2 t_2 
  on t_1.id= t_2.u_id;

which does not seem to work.

I've tried querying

select
  *
from table_1 t_1
left join join table_2 t_2 
  on t_1.id= t_2.u_id;

and simply using Export in mysql workbench but it only outputs 1000rows. I know that you can change the limit but my dataset if very large.

How to output to csv?

Answer 1

Your question lacks few details.

If you had tried the first command

select
  *
  INTO OUTFILE '/tmp/products.csv'
  ...

workbench (or so), you might have missed the error message - OR ELSE you missed to post the error here

1. Check if --secure-file-priv option is set. And you shall run your first choice itself.

2. If you run your second choice and then export from workbench, increase this

MySQL Workbench >> Edit >> Preferences >> SQL Queries tab >> Query Results (Limit Rows).