mysql - select * from tableA, tableB - problem

Question

is this a valid mysql statement ?

select * from TableA , TableB ;

if not what is the correct mysql statement for selecting the two tables.

why i think there's something wrong with this is i wrote a mysql statement

select * from TableA, TableB INTO OUTFILE 'c:/test.csv' FIELDS TERMINATED BY ','  ENCLOSED BY '"'  LINES TERMINATED BY '\n' ;

and it went to endless loop . when i terminated. the csv file had grown into GBs in size.

Answer 1

If you only want to take all of the records of TableA and then all of the records from TableB in sequential order, the union keyword might be of interest.

SELECT * FROM TableA
UNION
SELECT * FROM TableB 
    INTO OUTFILE 'c:/test.csv' 
    FIELDS TERMINATED BY ','  
    ENCLOSED BY '"'  
    LINES TERMINATED BY '\n' ;

Answer 2

It's not technically an endless loop - you are creating a cartesian product of the two tables -- that means that for each row in table a, every row in table b will be duplicated. This is almost never what you want to do -- it means that your output will be AXB rows, where A is the number of rows in table A and B is the number of rows in table B.

You either want to accomplish this task by selecting two statements and appending to the file twice, UNIONing the two tables together, or JOINing them together. Google for SQL Union, and INNER JOIN to learn more.

Answer 3

That query is going to produce a cross join, so the number of rows will be the count of Table A * the count of Table B.

If each of those tables had 100,000 rows in it, your CSV would end up with 10,000,000,000 rows, for example.