The used SELECT statements have a different number of columns

Question

For examples I don't know how many rows in each table are and I try to do like this:

SELECT * FROM members 
UNION 
SELECT * FROM inventory

What can I put to the second SELECT instead of * to remove this error without adding NULL's?

Answer 1

Put the columns names explicitly rather than *, and make sure the number of columns and data types match for the same column in each select.

Update:

I really don't think you want to be UNIONing those tables, based on the tables names. They don't seem to contain related data. If you post your schema and describe what you are trying to achieve it is likely we can provide better help.

Answer 2

you don't know the rows but you should know the columns!

Answer 3

you could do

SELECT *
from members
UNION
SELECT inventory.*, 'dummy1' AS membersCol1, 'dummy2' AS membersCol2
from inventory;

Where membersCol1, membersCol12, etc... are the names of columns from members that are not in inventory. That way both queries in the union will have the same columns (Assuming that all the columns in inventory are the same as in members which seems very strange to me... but hey, it's your schema).

UPDATE:

As HLGEM pointed out, this will only work if inventory has columns with the same names as members, and in the same order. Naming all the columns explicitly is the best idea, but since I don't know the names I can't exactly do that. If I did, it might look something like this:

SELECT id, name, member_role, member_type
from members
UNION
SELECT id, name, '(dummy for union)' AS member_role, '(dummy for union)' AS member_type
from inventory;

I don't like using NULL for dummy values because then it's not always clear which part of the union a record came from - using 'dummy' makes it clear that the record is from the part of the union that didn't have that record (though sometimes this might not matter). The very idea of unioning these two tables seems very strange to me because I very much doubt they'd have more than 1 or 2 columns with the same name, but you asked the question in such a way that I imagine in your scenario this somehow makes sense.

Answer 4

Are you sure you don't want a join instead? It is unlikely that UNOIN will give you what you want given the table names.

Answer 5

What is the Business Requirements here? Are you sure you want to return all records from these two seemingly unrelated tables? (Although in case of UNION you'll get distinct records).

Answer 6

I don't know how many rows in each table

Are you sure this isn't what you want?

SELECT 'members' AS TableName, Count(*) AS Cnt FROM members 
UNION ALL
SELECT 'inventory', Count(*) FROM inventory