CODEWITHSUNDEEP
sqljoin
Steve
Steve

Reputation: 5073

Join two tables to get all data

Suppose I have two tables:

Table 1

Col
1
3
4
5
6
9

and Table 2

Col
2
4
6
8

How can I Merge the two tables so I have 1-9 and if a number only appears in one table, the corresponding position in the other table has a null? Thank you.

Upvotes: 1

Views: 87

Answers (3)

Ramki
Ramki

Reputation: 519

You can try this one using union:

SELECT * FROM (SELECT col AS value FROM table1
UNION 
SELECT col AS value FROM table2)t1
ORDER BY value

try and post your comments:

Thanks

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

I'm assuming you want the numbers that actually exist in at least one of the tables, which won't give you a row with 7;

What you're looking for seems to be something like a FULL OUTER JOIN which works in pretty much any RDBMS except MySQL;

SELECT a.col col_a, b.col col_b
FROM Table1 a
FULL OUTER JOIN Table2 b ON a.col = b.col
ORDER BY COALESCE(a.col, b.col);

An SQLfiddle to test with.

Sadly, MySQL does not have FULL OUTER JOIN, so you'll have to do the same operation using a UNION between a LEFT JOIN and a RIGHT JOIN;

SELECT * FROM (
  SELECT a.col col_a, b.col col_b
  FROM Table1 a
  LEFT JOIN Table2 b ON a.col = b.col
  UNION
  SELECT a.col col_a, b.col col_b
  FROM Table1 a
  RIGHT JOIN Table2 b ON a.col = b.col
)z
ORDER BY COALESCE(col_a, col_b);

Another SQLfiddle.

Upvotes: 1

SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

If i am not wrong. you need records from both table. (please correct me if i am wrong)

Try following to get data from both tables:

select col from Table1
union
select col from Table2

select col from Table1
union all
select col from Table2

NOTE: UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

Upvotes: 0

Related Questions