Difference between joins - and ,or clause

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

SQL Server 2000 Forums

Transact-SQL (2000)

Difference between joins - and ,or clause

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-16 : 08:42:14

Whats the difference between the JOINS and AND, OR clause?...I tried to find from Help in T-SQL and BOL...But I couldnt understand properly..can you people explain how the joins work in comparison with an AND, OR clause...Mostly I use AND OR clauses in my queries...After posting my questions here and most of answers given to me were with joins.So now I'am trying to figure out how the Joins work...This may be a basic stuff but I want to know about it to use it in a proper way...

Thanks in advance.

Karunakaran

Don't wait for things to happen,Make them to happen...

Nazim
A custom title

1408 Posts

Posted - 2002-02-16 : 10:22:04

Hi Karuna,

When you use Joins the rows are filtered with the from clause itself then the where clause is applied whereas with only where clause the rows are filtered afterwards.

To explain on that , if there are total of 50,000 records and the join will yield about 10,000 records and there are some other where clauses too.
with joins the source of the data to filter is only 10,000 records coz the records are filtered before it goes to where . so when the where clause is applied it is applied only on the 10,000 records where as without joins it had to be applied on the whole 50,000 records thereby decreasing performance.

from BOL

The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/16/2002 10:23:32

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-16 : 11:07:16

Thanks Nazim...

But still I couldnt understand how the LEFT JOIN and INNER JOINS work.
To put it simple...I have 5 tables with following structure..
tbl1 recid,recname
tbl2 recid,splid
tbl3 recid,catid
tbl4 recid,cityid
tbl5 recid,countryid
Now I need to pick the recid from tbl1 whereas the
recid in tbl2,tbl3 should be present in tbl1,the recid in tbl2,tbl3 should also be present in either tbl4 or tbl5

I have the query like this...

select count(distinct a.recid) from tbl1 a LEFT JOIN tbl2 b ON
a.recid = b.recid AND b.splid IN (37) LEFT JOIN tbl3 c ON a.recid =
c.recid AND c.catid IN (66,67,68,69,70) INNER JOIN tbl4 d ON a.recid
= d.recid AND d.cityid IN (85,145) INNER JOIN tbl5 e ON a.recid =
e.recid AND e.countryid IN (33) WHERE a.middle=1 and COALESCE
(b.recruiter_id, c.recruiter_id, d.recruiter_id) IS NOT NULL

This query is also an answer posted to one of my questions here.I
made few changes,without understanding how it works...
But I'am not sure whether it meets my requirement...Unless I understand how the Inner joins and left joins work..I dont think I can solve the problem myself.

Karunakaran

Don't wait for things to happen,Make them to happen...

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-16 : 11:52:50

Think of the result set being a filtered cartesian product.

Say table a has 3 recs and table b 4 recs

a(i,j)
1,1
1,2
2,1

b(i,j)
1,1
1,2
3,1
3,2

the cartesian product is all possible combinations of rows from both tables i.e. 12 recs
a(i,j)b(i,j)
1,1,1,1
1,1,1,2
1,2,3,1
1,2,3,2
1,2,1,1
1,2,1,2
1,2,3,1
1,2,3,2
2,1,1,1
2,1,1,2
2,1,3,1
2,1,3,2

the inner join only takes rows for which the join criteria is fulfilled

a inner join b on a.i = b.i
gives
a(i,j)b(i,j)
1,1,1,1
1,1,1,2
1,2,1,1
1,2,1,2

the left outer join does the same thing but also returns at least one copy of every row from the left table - filling in the values from the other table with null if there is no matching row. In the above query there is no entry for 2,1 from table a so
a left outer join b on a.i = b.i
gives
a(i,j)b(i,j)
1,1,1,1
1,1,1,2
1,2,1,1
1,2,1,2
2,1,null,null
Another way of thinking of it is a left outer join returns every row from the left table and all rows from the right table the fulfill the join criteria.

Your query is of the form
a(i,j)b(i,j)
1,1,1,1
1,1,1,2
1,2,1,1
1,2,1,2
2,1,null,null

a left outer join b on a.i = b.i and b.i = 1
which would just drop the rows which do not satisfy the criteria and add any missing rows from a.

Note that this is different from
a left outer join b on a.i = b.i
where b.i = 1

This would perform the left outer join first then filter on the where clause - it would omit all rows where b.i <> 1 - i.e. drop all the rows where b.i = null and so would be the same as an inner join.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-16 : 12:09:22

As of now I feel like I'am clear with this...If I have any doubts I'll come back...

Thank you guys...

Karunakaran

Don't wait for things to happen,Make them to happen...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources