Select t1.f1, t2.f1 From t1, t2 where t1.f1=t2.f1

Select t1.f1, t2.f1 From t1 inner join t2 on t1.f1=t2.f1

select ......
from Table1
  join Table2 on ....
  join Table3 on ....
  join Table4 on ....
  join Table5 on ....
  join Table6 on ....
  join Table7 on ....
  join Table8 on ....
  join Table9 on ....
where <условия на Table1>

Select * From 
Table1, 
Table2, 
Table3, 
Table4, 
Table5, 
Table6, 
Table7, 
Table8
 
where  Table1.f1=Table2.f1
          ....
          ....

select * 
INTO #u
from (
Select 1 rid,'Вася' name
Union
Select 2 rid,'Фёдор' name
Union
Select 3 rid,'Оля' name
Union
Select 4 rid,'Наташа' name
) u

select identity(int, 1,1) as id, * 
INTO #t
from (
Select 0 fl, 1 uid, 3 sale  
Union
Select 0 fl, 1 uid, 2 sale  
Union
Select 0 fl, 2 uid, 4 sale
Union
Select 1 fl, 2 uid, 1 sale
Union
Select 1 fl, 2 uid, 5 sale
Union
Select 1 fl, 3 uid, 5 sale
Union
Select 1 fl, 3 uid, 6 sale
) t

select #u.name, count(#t.id) Sales
from #u, #t
where #u.rid *= #t.uid
	and fl = 0
	and #u.rid = 3
GROUP BY #u.name

select #u.name, count(#t.id) Sales
from #u
	left JOIN #t on #u.rid = #t.uid
where fl = 0 and 
	#u.rid = 3
GROUP BY #u.name

select #u.name, count(#t.id) Sales
from #u
left JOIN #t on #u.rid = #t.uid and fl = 0 
where #u.rid = 3
GROUP BY #u.name

select *
from #u, #t
where #u.rid *= #t.uid
	and #t.fl = 0
	and #u.rid = 3

rid         name   id          fl          uid         sale        
----------- ------ ----------- ----------- ----------- ----------- 

3           Оля    NULL        NULL        NULL        NULL

select *
from #u
	left JOIN #t on #u.rid = #t.uid
where
	#t.fl = 0
	and #u.rid = 3

rid         name   id          fl          uid         sale        
----------- ------ ----------- ----------- ----------- ----------- 

  |--Nested Loops(Left Outer Join, WHERE:([#t].[uid]=[#u].[rid]))

       |--Table Scan(OBJECT:([tempdb].[dbo].[#u____000000000015]), WHERE:([#u].[rid]=3))

       |--Table Scan(OBJECT:([tempdb].[dbo].[#t____000000000015]), WHERE:([#t].[fl]=0))

  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([#t].[uid])=([#u].[rid]), RESIDUAL:([#t].[uid]=[#u].[rid]))

       |--Sort(ORDER BY:([#t].[uid] ASC))

       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#t____000000000015]), WHERE:([#t].[fl]=0))

       |--Table Scan(OBJECT:([tempdb].[dbo].[#u____000000000015]), WHERE:([#u].[rid]=3))

select *
from #u, #t
where #u.rid *= #t.uid

select *
from #u
	left join #t on #u.rid = #t.uid

Результаты ОДИНАКОВЫЕ:

rid         name   id          fl          uid         sale        
----------- ------ ----------- ----------- ----------- ----------- 

1           Вася   1           0           1           2
1           Вася   2           0           1           3
2           Фёдор  3           0           2           4
2           Фёдор  4           1           2           1
2           Фёдор  5           1           2           5
3           Оля    6           1           3           5
3           Оля    7           1           3           6
4           Наташа NULL        NULL        NULL        NULL

select *
from #u
	left JOIN #t on #u.rid = #t.uid and #t.fl = 0
where
	#u.rid = 3

select *
from #u
	left JOIN #t on #u.rid = #t.uid
where
	#t.fl = 0

rid         name   id          fl          uid         sale        
----------- ------ ----------- ----------- ----------- ----------- 

1           Вася   1           0           1           2
1           Вася   2           0           1           3
2           Фёдор  3           0           2           4

select *
from #u
	left JOIN #t on #u.rid = #t.uid and #t.fl = 0

rid         name   id          fl          uid         sale        
----------- ------ ----------- ----------- ----------- ----------- 

1           Вася   1           0           1           2
1           Вася   2           0           1           3
2           Фёдор  3           0           2           4
3           Оля    NULL        NULL        NULL        NULL
4           Наташа NULL        NULL        NULL        NULL