mysql - Confusion in JOIN Query -

  select i.invoice_date inv,           i.invoice_id,           i.total_amount,           oi.invoice_date oi_inv,           oi.offline_invoice_number,           oi.invoice_amount      ci_invoices                left join ci_offline_invoice oi                          on oi.customer_id = 9     i.customer_id = 9  order i.invoice_date     limit 10 

this query give result

as see in screenshot have 1 row in highlighted section , want result according date:

2014-02-03 2014-01-16 2014-01-16 2014-01-16 2014-01-16 2014-01-16 .......... 

but got date 2014-02-03 every time(limit 10)

try this

select i.invoice_date inv,           i.invoice_id,           i.total_amount,           oi.invoice_date oi_inv,           oi.offline_invoice_number,           oi.invoice_amount      ci_invoices                left join ci_offline_invoice oi                          on oi.customer_id = i.customer_id    i.customer_id = 9  order i.invoice_date     limit 10 

** edit **

they not same thing.

consider these queries:

select i.invoice_date inv,           i.invoice_id,           i.total_amount,           oi.invoice_date oi_inv,           oi.offline_invoice_number,           oi.invoice_amount      ci_invoices                left join ci_offline_invoice oi                          on oi.customer_id = i.customer_id    i.customer_id = 9  order i.invoice_date     limit 10 

and

select i.invoice_date inv,           i.invoice_id,           i.total_amount,           oi.invoice_date oi_inv,           oi.offline_invoice_number,           oi.invoice_amount      ci_invoices                left join ci_offline_invoice oi                          on oi.customer_id = 9     i.customer_id = 9  order i.invoice_date     limit 10 

the first return ci_invoices , lines, if any, customer_id =9.

the second return ci_invoices, customer_id 9 have lines associated it.

on inner joins interchangeable, , optimizer rearrange them @ will.

on outer joins, not interchangeable, depending on side of join depend on.