What is exactly Left Outer Join in Database View

[sieusaopolo15]

I created two tables in Database View like below here:

=> Results returns to me 45,478 records which is normal because it returns matched records and all records in table Resource Aggregate Weekly (raw).

Then, I updated the Left Outer value of resource_aggregate_weekly table to true and time_card to false. I should think it will give the same result because in this Document that Joined tables are ordered left to right from lowest to highestOrdervalues but it gives different result it's not 45,478 anymore it is 1,785. Do I configure something wrong or am I not understanding this correctly ?

- Updated table views:

- Results

[Community Alums]

Hi @sieusaopolo15 ,

Here's a few hidden rules that will make your life easier once you know them:

• Your "Where" clauses may only refer to view tables that were evaluated prior to the currently running view table

• The evaluation order is determined by the "Ordering" (lower = earlier), but there's a catch!

• Left-joins always run last, regardless of the order value. They are basically happening in an entirely different phase that comes after the primary phase

• Ordering is still used with left-joins, but only to determine execution order relative to other left-joins

• Because left joins run last, view tables that are not left-joined may never refer to left-joined view tables

You can refer to below links for example : https://www.servicenow.com/community/itsm-forum/database-view-left-join-help/m-p/644370

https://www.servicenow.com/docs/bundle/yokohama-platform-administration/page/use/reporting/task/exam...

And video : https://www.servicenow.com/community/in-other-news/database-views-left-joins-and-filters-video/ba-p/...

[sieusaopolo15]

Hi Sandeep,

Thanks for some hidden rules but I found it confusing if the Left-joins are always run last, regardless of the order value then when do they run last ? Because what I'm seeing now is the orders are still being used which means the Order logic is applied in my first attempt which is returns 45,478 records but after I changed Left Join to True value in resource_aggregate_weekly table, the logic now is based on the run last but the not ordering if I understand correctly.