The reason is quite simple:
You save a lot of SQL executing time.
Another positive side is:
The amount of code lines is also less then the other two solutions would need.
You need to understand the logic behind analytical functions and you need to practice with them. :)
What was my problem?
I had some incomplete data to fix. Some rows of one column in my table were not filled. For my luck I did know that the previous row included the right value.
Here the example:
/* Using the LAG function */ select OE2.ID, OE2.CAR_NO, CASE WHEN OE2.CAR_NO IS NULL THEN LAG(OE2.CAR_NO, 1, 0) OVER (ORDER BY OE2.ID) ELSE OE2.CAR_NO END as CAR_NO_FIXED from TBL_ORDER_LIST OE2 /* Using the SUB-Select */ select OE2.ID, OE2.CAR_NO, CASE WHEN OE2.CAR_NO IS NULL THEN ( SELECT OE1.CAR_NO FROM TBL_ORDER_LIST OE1 WHERE OE2.ID = OE1.ID-1 ) ELSE OE2.CAR_NO END AS CAR_NO_FIXED from TBL_ORDER_LIST OE2
The more rows you have in the table the bigger will be the difference in execution time.
If you want to know more about the LAG function.
Try this link: