sql query help

I have two tables.  table 'a' and table 'b'

each table has 5 columns and each one has a column called account which is what I want to do a join on.

how can I return all rows in table 'a' where account number is not found in table 'b' and vice versa.
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Select * From Table_A a 
Left Join Table_B b
On a.account = b.account
Where b.account IsNull

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
select case when a.account is null then 'B' else 'A' end as srcTable, isnull(a.account, b.account) account
from tbl_a a
full join tbl_b b on a.account = b.account
where a.account is null or b.account is null

Open in new window

The left join will only return one of the tables, if you want both of them you need the full join.
Mike EghtebasDatabase and Application DeveloperCommented:
correction:
Select * From Table_A a 
Left Join Table_B b
On a.account = b.account
Where a.account IsNull
Or b.account IsNull 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
select a.* from a
left join b on a.account  = b.account
where b.account is null

>> and vice versa

select b.* from b
left join a on b.account  = a.account
where a.account is null
Kanti PrasadCommented:
hi

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.account = TableB.account
WHERE TableB.account IS null

SELECT * FROM TableB
LEFT OUTER JOIN TableA
ON TableB.account = TableA.account
WHERE TableA.account IS null

Read the below link you will get a good idea
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
Mike EghtebasDatabase and Application DeveloperCommented:
Revising the good solution from Kyle, I have:create table
Using:
create table #Table_A(account_number int, field1 varchar(10));
Insert #Table_A values
(1, 'Robert')
,(3, 'Mike')
,(4, 'Jane')
,(5, 'Peter');
GO
create table #Table_B(account_number int, field1 varchar(10));
Insert #Table_B values
(1, 'Robert')
,(2, 'Karan')
,(4, 'Jane')
,(6, 'Sheila');
Select * From #Table_A;
Select * From #Table_B;

Select a.account_number , a.field1,b.account_number, b.field1
From #Table_A a Full Join 
--Cross APPLY (Select b.account_number
#Table_B b
on a.account_number = b.account_number
Where a.account_number Is Null
Or b.account_number Is Null

Select * From #Table_A;
Select * From #Table_B;

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Depends on how you define 'and vice versa'.

Assuming 'and vice versa' means you want both in the same set, then Kyle's first comment is correct.
Assuming different sets, Kanti's first comment is correct.

btw, if you go to images.google.com and search for 'SQL JOIN' you'll see hundreds of lovely pictures that illustrate the different JOIN types and provide sample T-SQL.  They're all copyrighted so we can't just post one here.
Mike EghtebasDatabase and Application DeveloperCommented:
Hi  jamesmetcalf74,

To get quick and accurate solutions, please take a look at this link:

http://www.experts-exchange.com/articles/18499/Top-10-Ways-to-Ask-Better-Questions.html

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.