mysql - union with creating demarcated field

Question

I need UNION two tables with creating new field, where 1 for first table, and 2 for second.

I tried

(
    SELECT field, 1 AS tmp
    FROM table1
)
UNION
(
    SELECT field, 2 AS tmp
    FROM table2
)

But in result, tmp field was full of "1".

How it can be implemented?

Answer 1

You are very close

Create YourNewTable
SELECT field, 1 AS tmp 
    FROM table1 
UNION ALL
    SELECT field, 2 AS tmp 
    FROM table2 

Answer 2

Your query should work fine. The only thing you should change is UNION should be UNION ALL to give better performance. Without the ALL it defaults to UNION DISTINCT which causes the rows to be compared for duplicates*, but the way you have constructed them guarantees that there cannot be duplicates so this extra check is a waste of time. Here is some test code I used to verify that what you are doing ought to work:

CREATE TABLE table1 (field NVARCHAR(100) NOT NULL);
INSERT INTO table1 (field) VALUES
('foo1'),
('bar1'),
('baz1');

CREATE TABLE table2 (field NVARCHAR(100) NOT NULL);
INSERT INTO table2 (field) VALUES
('foo2'),
('bar2'),
('baz2');

SELECT field, 1 AS tmp
FROM table1
UNION ALL
SELECT field, 2 AS tmp
FROM table2

Result:

'foo1', 1
'bar1', 1
'baz1', 1
'foo2', 2
'bar2', 2
'baz2', 2

If you only get rows where tmp was equal to 1, maybe your table2 was empty?

---

*See the documentation for UNION.

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.