Take the 2-minute tour ×

How can I join table a and table b and get records for each? Not an actual join... not sure what this is called.

So if I have 3 records in a, and 5 records in b, I want 8 records back.

In a record for a, all b fields can be null. In a record for b, all a fields can be null.

edit: My tables have different fields.

Error Code: 1222. The used SELECT statements have a different number of columns
share|improve this question
The error is because your both select don't have the same columns. They have to be the same type and in the same order. –  Marc Oct 12 '12 at 18:40
add comment

3 Answers

up vote 2 down vote accepted

Like the others mentionned, you need an union

SELECT intColumn, varcharColumn, intColumn FROM a
SELECT intColumn, varcharColumn, 0 FROM b

but you must have the same number of columns and they must also have similar data types.

Here's a good tutorial about it

Also, if you want columns that are not in both tables, you can fill with nulls or constants.

share|improve this answer
Can I deal with my columns being different by selecting null values to make them match? SELECT NULL as field_that_this_table_doesnt_have –  Stephen Sarcsam Kamenar Oct 12 '12 at 18:48
Yes you can, I updated my answer. You can add constants in your select. I don't have MySQL but it works in sql server 2010, i just tried. –  Marc Oct 12 '12 at 18:52
add comment

You want a UNION:

SELECT something FROM a
SELECT something FROM b
share|improve this answer
Use UNION ALL to be sure that duplicates are not removed if the tables contain the same values. –  Michael Berkowski Oct 12 '12 at 18:36
add comment

Try this

LEFT JOIN b ON a.id1 = b.id2
RIGHT JOIN b ON a.id1 = b.id2

Just make sure, that A and B have different IDs

Edit: Working Fiddle

You can also use some other field other then id which are not same in two table

Edit: Updated fiddle

share|improve this answer
sqlfiddle? awesome, thanks. –  Stephen Sarcsam Kamenar Oct 12 '12 at 18:55
add comment

Your Answer


