How to join two table with customize column?

Question

I have two table.

• Table A with 4 columns (Category,Product_X,Price,Color),
• Table B with 4 columns (Category,Product_Y,Price,Weight).

I want to select all data from two tables and customize columns as follow (change name column Product_X and Product_Y to Product, add two column Color and Weight,.etc..).

Please see image

I think i should use JOIN, but I still have no idea about this issue.

Answer 1

You need to use UNION ALL:

SELECT
    Category,
    Product_X AS Product,
    Price,
    Color,
    NULL AS Weight
FROM TableA

UNION ALL

SELECT
    Category,
    Product_Y,
    Price,
    NULL,
    Weight
FROM TableB