sql - Standard approach when mixing several INNER with several LEFT OUTER JOINs -


if have several lojs , several inner joins there correct standard syntactical structure should use?

example scenario

  • 5 tables #a - #e userid column , each additional column measure - measurea in table #a, measureb in table #b etc.
  • tables #a, #b, #c have same set of userids
  • tables #d , #e have different subsets of set of userids in #a-#c.

is correct structure use:

select      #a.userid,     #a.measurea,     #b.measureb,     #c.measurec,     d = coalesce(#d.measured,0.),     e = coalesce(#e.measuree,0.)             #a     join #b         on #a.userid = #b.userid     join #c         on #a.userid = #c.userid     left outer join #d         on #a.userid = #d.userid     left outer join #e         on #a.userid = #e.userid 

or should lojs applied within subquery on #a?

select      x.userid,     x.measurea,     #b.measureb,     #c.measurec,     x.d,     x.e             (     select       #a.userid,       #a.measurea,       d = coalesce(#d.measured,0.),       e = coalesce(#e.measuree,0.)     #a          left outer join #d             on #a.userid = #d.userid         left outer join #e             on #a.userid = #e.userid     ) x     join #b         on x.userid = #b.userid     join #c         on x.userid = #c.userid 

when using left outer joins, intention 1 of tables keeping of rows, regardless of matches in other tables.

my preferred structure put table first:

select . . . <really important table> t left outer join      . . . 

this doesn't work if have inner joins later in from clause, because these filter out rows no matches.

in terms of query, think first expect. second happens want, because joining on id column. structure dangerous. if 1 of subsequent inner joins on column #e, (inadvertently) change left joins inner joins.

so, put inner joins first, left outer joins.


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -