0

I need to combine two tables with different number of columns in mysql .... is there any tricks to do that ?

Siva Shankari
  • 9
  • 2
  • 5
  • Possible duplicate of [The used SELECT statements have a different number of columns (REDUX!!)](https://stackoverflow.com/questions/3655708/the-used-select-statements-have-a-different-number-of-columns-redux) – Suhas Bachhav Jun 30 '18 at 05:28
  • 2
    Add null or other default values to the SELECT with fewer columns – Joakim Danielson Jun 30 '18 at 05:55

1 Answers1

0

As @Joakim Danielson say, you can try to use UNION ALL combine two query. Add NULL with fewer columns.

CREATE TABLE A(
    col1 int,
    col2 varchar(100),
    col3 datetime
);

insert into a values (1,'test1','2017-01-01 01:00:00');

CREATE TABLE B(
    col1 int
);

insert into b values (3);

For example, there are three columns in A table, one column in B table.

look like this.

SELECT col1,col2,col3
FROM A
UNION ALL 
SELECT col1,null,null 
FROM B

NOTE:

  • if you UNION ALL need to make sure columns have the same type, the above example A.col1 and B.col1 types should be the same.
  • if A.col1 and B.col1 don't have same types you can use CAST function make they are same type.
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Just to add when using Union and Union all, the main difference is that Union all will not eliminate duplicate rows, in fact it retrieves all rows from all tables fitting the specified query and combines into a table. :) – Dodi Jun 30 '18 at 10:47