Left join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left join

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-09-09 : 06:57:31

I want to join two table through left join.
Prob: 1st table name is A and second table name is B.
I want all the records from A. so i do the left join. but when i add where condition regading some field of B table. It gives me only the record present in Table B.Not all the record of table A.
Example:
Select
A.a,
A.b,
B.a,
B.b,
B.c
from A
left join B
on A.a=B.a
where B.b=some_condition and B.c=some_condition

now problem is that it does not give me all the records from Table A but only the records that are common between table A and B.But i need all the records of table A.

Can someone please help me?I need it very urgently.Also please explain why this is happen.

Thanks
Kalyan

kalyan Ashis Dey

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-09 : 07:36:39

put your where condition as a part of join condition itself.

on A.a=B.a and B.b=some_condition and B.c=some_condition

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-09-09 : 08:45:12

Thanks a lot.It works fine.But can you please tell me the reason why where condition does not work here
Thanks
Kalyan

kalyan Ashis Dey

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-09-09 : 09:50:36

The main SQL clauses are logically evaluated in the following order:

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

In you case:


FROM A
	LEFT JOIN B
		ON A.a = B.b
WHERE B.b = some_condition
	AND B.c=some_condition

1. the FROM clause will produce NULLs for the outer B columns
2. the WHERE clause will then remove those rows, effectively causing an INNER JOIN, as NULL does not equal some_condition.

Knowing the order of evaluation also helps explain why you can reference SELECT expressions in the ORDER BY clause but not in the WHERE clause etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:58:12

see below

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources