2

I have two tables of customer information, Tables A and B. They share no columns besides the ones found in the query, and all customers from Table B can be found in the much larger Table A.

Whenever I try to combine the customer data I end up eliminating the customers in A but not in B, or I end up getting way too many rows by getting repeat entries. How do I join the tables so I retain all of Table A and only add Table B columns to the customers found in both? Here is my best attempt:

select a.*, b.*
from table a
join table b
on a.id = b.id
where a.name = b.name ##OR?

**That will give me everything I need EXCEPT it only gives me the customers in both tables. I think I need to include some type of or statement to allow for cases where the customer is found in Table A but not Table B but I'm not sure how to set that up.

user2891518
  • 189
  • 3
  • 13
  • 4
    `left join`, then: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Marc B Aug 05 '15 at 15:13
  • Possible duplicate: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins – Mark Bolusmjak Aug 05 '15 at 15:14
  • left join doesn't work. it joins the tables but cuts out the customers from A that are not in B – user2891518 Aug 05 '15 at 15:17
  • Note when using outer joins. Where clause should only be placed on the table returning all records; otherwise you negate the outer join. In these cases, add the limiting criteria to the join itself as an and statement. That way the null records from the outer join are kept; as the engine applies the limit on data before it generates the join results. So in your case `on a.id = b.id and a.name = b.name` no where clause. Using the where clause as you have it eliminates the NULL records created by the left outer join (assuming you use one). – xQbert Aug 05 '15 at 15:18
  • Perfect! Thanks, xQbert! – user2891518 Aug 05 '15 at 15:20

2 Answers2

4

Here's a good visual cheat sheet. http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

enter image description here

FirebladeDan
  • 1,069
  • 6
  • 14
  • Yup! +This is a good interview practice tool.During interviews sometimes they'll ask you to draw the relationship or if you go out of your way to draw the two relationships they know that you understand the logic. – FirebladeDan Aug 05 '15 at 15:36
2
select id, NULL as somecol1, NULL as somecol2 --add more columns as needed
from tablea
UNION
select id, somecol1, somecol2 --add more columns as needed
from tableb
where id in (select id from tablea)

You can use union to achieve what you need.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58