sql - Join rows from two different queries -

i have table sales_table id,date , sales fields.

no          date          sales
----------------------------------------------------
1          1-jan          10,000
2          3-jan          12,500
3          4-jan          8,000
4          5-jan          12,000
5          8-jan          10,000
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
"          "          "
100          13-mar          4000

the date unique not in series. no unique , in series incremented no of each higher date.

i looking difference between sales of current , previous date. like

no               date               sales               diff              
------------------------------------------------------------------------------------------
1               1-jan               10,000               0              
2               3-jan               12,500               2500              
3               4-jan               8,000               -4500              
4               5-jan               12,000               4000              
5               8-jan               10,000               -2000              

i using sql query-

select t1.no,t1.date,t1.sales (t1.sales-2.sales) diff  sales_table t1,sales_table t2  where(t1.no=t2.no+1) order t1.date 

this works fine except records starting no 2.

so have written another sql query -

select no,date,sales,sales-sales diff  sales_table  where(no=1)  

which outputs as- 1, 1-jan,10000,0.

how can join rows both these queries?

there couple of different options depending on database. 1 option modify existing query use outer join:

select t1.no,t1.date,t1.sales,(t1.sales-coalesce(t2.sales,t1.sales)) diff  sales_table t1     left join sales_table t2 on t1.no=t2.no+1 order t1.date 

• sql fiddle demo