sql join two table

Question

TABLE A >>
uid name
1   test1
2   test2
3   test3
4   test4

TABLE B >>
uid address
1   address1
2   address2
4   address3

RESULT
1   test1   address1
2   test2   address2
3   test3
4   test4   address3

Can anyone show me how to write a query and fetch the result as above, Thanks very much! i have tried join, left and right join. all result nothing.

Solution 1

You can write left outer join between this two tables Best way to understand is check the below image

Query for your requirement

SELECT A.uid, A.name, B.address FROM A LEFT JOIN B ON A.uid=B.uid 

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

Solution 2

SELECT A.uid, A.name, B.address FROM A LEFT OUTER JOIN B ON A.uid = B.uid

Solution 3

You say you tried a left join but didn't give any attempts --- one of the first logical attempts would have been:

SELECT A.uid, A.name, B.address
FROM A
LEFT JOIN B ON A.uid=B.uid

Hey presto! it gives you what you were after.

Solution 4

You can use any join.I write this query for full join.

select A.uid,A.name,B.address from A FULL JOIN B ON A.uid = B.uid

Solution 5

I guess you're after an empty value if there is no value for B, that is having the same uid in A.

If this is the case, IFNULL will return the default value you specified in case the parameter is null (ISNULL is used in MSSQL):

SELECT A.value, IFNULL(B.value, '')
FROM A LEFT JOIN B
ON A.uid = B.uid

This will produce something like:

test1   address1
test2   address2
test3   
test4   address3

View more solutions

Comments

[onedaywhen]

Those are diagrams of set operations, not joins e.g. the one bottom left is A UNION B, the one in the middle is A INTERSECT B, etc.

[Bill]

i love your diagram, explain everything so well. Thanks very mcuh