CODEWITHSUNDEEP
sqloracle-database
schneiju
schneiju

Reputation: 125

SQL - not sure how to join tables

I'm trying to join two tables like this:

Table A

ID    Value1
1     A
2     B
3     C

Table B

ID    Value2
1     A
3     B
4     C

Result should be:

ID    Value1  Value2
1     A       A
2     B       null
3     C       B
4     null    C

I.e. join Table A to Table B on ID. If ID doesn't exist in Table A, add the ID from Table B.

The closest I've come is:

SELECT 
    a.ID, a.Value1, b.Value2 
FROM 
    TableA a 
OUTER JOIN 
    TableB b ON a.ID = b.ID

That gives me the new rows from TableB, but the ID is null.

How can I accomplish this?

Upvotes: 1

Views: 69

Answers (3)

user6133527
user6133527

Reputation:

Remember the basic for joining different tables

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

For your case:

SELECT a.value1, b.value2 
FROM TableA a 
FULL OUTER JOIN TableB b ON a.ID=b.ID

remember full outer join

The FULL OUTER JOIN keyword returns all rows from the table (tableA) and from the table (tableB) and the FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

You are very close, you just need a little push in the right direction:

SELECT COALESCE(a.ID, B.ID) As ID, a.Value1, b.Value2 
FROM TableA a 
FULL OUTER JOIN TableB b ON a.ID=b.ID

The COALESCE function returns the first parameter it gets that is not null. since this is a full outer join, a.id will be null on one row and b.id would be null on a different row.

Upvotes: 5

nhouser9
nhouser9

Reputation: 6780

Try this: 

SELECT *
FROM TableA A
FULL OUTER JOIN TableB B
ON A.ID = B.ID;

Just a note: you should not name your tables in SQL with spaces in them.

Upvotes: 0

Related Questions