CODEWITHSUNDEEP
mysqlsqlunion
NaN
NaN

Reputation: 163

UNION unexpected results?

Let's say we have:

table 1

a (int) | b (int)
--------|--------
1       | 4
2       | 4

table 2

c (text)   d (text) 
---------|---------
hoi      | hi

Query:

SELECT * FROM table1 
UNION 
SELECT * FROM table2

yields

a     | b 
------|--------
1     | 4
2     | 4
hoi   | hi

At least, from the query I just ran on mysql

I'd expect (1, 4, NULL, NULL). Why doesn't this give an error?

Upvotes: 0

Views: 126

Answers (2)

Barmar
Barmar

Reputation: 780798

UNION just appends the rows of one query to the rows of the other. As long as the two queries return the same number of columns, there's no error. The column names always come from the fist query. If the datatypes are different, it finds a common type that they can all be converted to; in your example, it converts the int columns to text (MySQL is loose about this, some other databases require that you use explicit CAST() calls to get everything to the same type).

Since your queries each return two columns, the result contains two columns, using the column names from table1.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is a bit long for a comment.

I just tested this on MySQL 8.0 and SQLite and it returns:

a   b
1   4
2   4
hoi hi

I find this surprising. I would expect the columns to be given an integer type and for there to be either a type conversion error or 0 for the third row. Well, actually the SQLite results isn't that strange, because types are much more fungible in SQLite.

SQL Server and Postgres give errors that I would expect -- type conversion errors that cause the query to fail.

Upvotes: 0

Related Questions