0

I have two A1 and A2 tables wit single column.

A    B
-    -
1    3
2    4
3    5
4    6

I need to select records from A which are not in B. For this i tried select A from [A1] as a left outer join A2 as c on a.[A]=c.[B] but it select 1 and 2 more than one time, i want unique results only, want 1 and 2 only. Refereed few links Outer join but not able to understand fully. I know this is silly question but i am new with joins.

Community
  • 1
  • 1
Hot Cool Stud
  • 1,145
  • 6
  • 25
  • 50

2 Answers2

1

What about not in combined with Distinct so you're not getting any duplicates

select Distinct A
from A1
where A not in (select B from A2)
Milen
  • 8,697
  • 7
  • 43
  • 57
  • which will be better join or not in when no of rows is greater than 20k. – Hot Cool Stud Apr 14 '14 at 08:47
  • 1
    it all depends on your data. You can try comparing the two solutions and pick the one that suits you most. http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join – Milen Apr 14 '14 at 08:50
0

The SQL request in your question should normally give 1,2,3,4 as results , the following picture shows how different joins work :
enter image description here

So if you want to display only records that are in A but not in B, it is : 

SELECT distinct A from A1 a LEFT JOIN B1 b   
ON a.A = b.B  
WHERE b.B is NULL

The following picture explains more about different joins and their SQL syntax implementations :
enter image description here

Community
  • 1
  • 1
mounaim
  • 1,132
  • 7
  • 29
  • 56