How to append results from multiple tables in SQL?

Question

I have 3 tables in singlestore (Memsql) that I want to query and have the results appended.

Orders

part_number	received_qty	update_ts
partA	10	Monday

PullSheet

part_number	pull_qty	return_qty	pull_date
partA	3	0	Tuesday

VarianceSheet

part_number	pull_qty	return_qty	pull_date
partA	1	2	Wednesday

I would like a stored procedure that takes a part_number, limit, and offset input. Query these tables for part_number return all the results sorted by their timestamps and then apply limit and offset. Result should be like below.

Result

event_name	part_number	received_qty	pull_qty	return_qty	date
order	partA	10	null	null	Monday
pullsheet	partA	null	3	0	Tuesday
variancesheet	partA	null	1	2	Wednesday

What would be the best way to achieve this? I looked into Unions but that requires the tables to have the same number of columns. That is not the case for my tables.

Answer 1

If the table schema is fixed and not going to be changed, you can do the following:

SELECT 
  'order' as event_name
  ,part_number
  ,received_qty
  ,NULL as pull_qty
  ,NULL as return_qty
  ,update_ts as date
FROM Orders

UNION ALL

SELECT 
  'pullsheet' as event_name
  ,part_number
  ,NULL as received_qty
  ,pull_qty
  ,return_qty
  ,pull_date as date
FROM PullSheet

UNION ALL

SELECT 
  'variancesheet' as event_name
  ,part_number
  ,NULL as received_qty
  ,pull_qty
  ,return_qty
  ,pull_date as date
FROM VarianceSheet

Using UNION ALL here for fast performance as there is no need to check for duplicity (event_name will be distinct for each table at least)