12
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.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
Bill
  • 17,872
  • 19
  • 83
  • 131

5 Answers5

20

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.

alt text

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

Community
  • 1
  • 1
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • 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. – onedaywhen Feb 07 '12 at 08:26
  • i love your diagram, explain everything so well. Thanks very mcuh – Bill Feb 07 '12 at 23:05
2
SELECT A.uid, A.name, B.address FROM A LEFT OUTER JOIN B ON A.uid = B.uid
Kai G
  • 3,371
  • 3
  • 26
  • 30
1

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.

mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194
1

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
Suresh
  • 1,494
  • 3
  • 13
  • 14
1

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
Pavel Donchev
  • 1,809
  • 1
  • 17
  • 29