16

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
good_evening
  • 21,085
  • 65
  • 193
  • 298
  • 8
    Why would you want to union two tables of completely different contexts? – Joe Sep 01 '10 at 21:01
  • 5
    You shoud never use select * in any query that will run on production. You should always specify only the columns you need both for maintainibility and performance. – HLGEM Sep 01 '10 at 21:10

6 Answers6

16

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.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • I know nothing about any names, nothing. Is it still possible? – good_evening Sep 01 '10 at 20:57
  • 1
    @hey: Use `DESC your_table_name` to find out the columns and their data types before writing the UNION query. – OMG Ponies Sep 01 '10 at 20:59
  • 1
    @hey: Umm... sorta non-sequitor there... if you type the column names in manually, you will reduce the risk of SQL injection. – FrustratedWithFormsDesigner Sep 01 '10 at 21:11
  • @Frus SQL injection!?!?!? What in tarnation are you talking about? `SELECT *` vs. `SELECT Col1, Col2` has absolutely **nothing** to do with SQL injection. – ErikE Sep 14 '10 at 16:29
  • @Emtucifor: It was `hey` that brought up the topic of SQL injection. My guess is that `hey` thought `SELECT * ` could lead to SQL injection. I used to have a manager who would also worry about that so insisted that column names be always be explicit. It didn't make much sense to me, but it felt like that guy and `hey` took the same classes in school... – FrustratedWithFormsDesigner Sep 14 '10 at 17:09
  • 1
    @Frus oops, I meant to address that to hey, sorry about that. Let me try again. – ErikE Sep 14 '10 at 18:18
  • @hey SQL injection!?!?!? What in tarnation are you talking about? SELECT * vs. SELECT Col1, Col2 has absolutely nothing to do with SQL injection. – ErikE Sep 14 '10 at 18:19
6

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.

FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
  • 2
    Why not use `NULL` instead of 'dummy1' and 'dummy2'? – dan04 Sep 01 '10 at 21:20
  • Even if this would work, you still need to specify column names as the columns may or may not be in the same order on both tables (or stay in the same order forever). Plus if inventory then had anopther column added, the union would break. Never do a UNOIN without specifying the columns. Also all unions should be considered as candiates fro UNION ALL which is much more performant if there is no possiblity of dupes that need to be filtered out. – HLGEM Sep 01 '10 at 22:20
  • Telling how to do something that is so obviously incorrect is hurting the poor chap asking the question, not helping him. – ErikE Sep 14 '10 at 16:28
  • @Emtucifor: Considering his question barely made sense (union two completely different tables), I don't really see what's wrong with this answer. It would work for what he wants to do. *WHY* he wants to do that is something else entirely. – FrustratedWithFormsDesigner Sep 14 '10 at 17:01
  • IF you use Null for the dummy values you wil probably want to cast it to the correct datatype. Null is considered an int if not specifially cast to something else. – HLGEM Oct 04 '14 at 12:17
3

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
2

Try this

(SELECT * FROM members) ;
(SELECT * FROM inventory);

Just add semicolons after both the select statements and don't use union or anything else. This solved my error.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Saurabh Jadhav
  • 21
  • 1
  • 2
    This is slightly different than a SQL statement with `UNION` in it which returns one result combined from two tables whereas this returns two results. In most cases this needs to be handled differently on the client side and thus cannot be used as a direct replacement of `UNION`. In addition, the parentheses around the SQL aren't needed. – markusjm Sep 28 '21 at 05:26
0

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
ErikE
  • 48,881
  • 23
  • 151
  • 196
0

Each SELECT statement within the MySQL UNION ALL operator must have the same number of fields in the result sets with similar data types Visit https://www.techonthenet.com/mysql/union_all.php

user9862225
  • 1