Join - comparing default join syntax and ANSI 92 syntax =

About

Join syntax for two joins is shown below. The first shows the default behavior where the join is defined in the WHERE clause, the second shows the same join in the FROM clause using the ANSI 92 standard.

Examples

Default join syntax

SELECT
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY'),
sum(Invoice_Line.days * Invoice_Line.nb_guests * Ser
vice.price)
FROM
Resort,
Sales,
Invoice_Line,
Service,
Service_Line
WHERE
( Sales.inv_id=Invoice_Line.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Resort.resort_id=Service_Line.resort_id )
AND ( Service.sl_id=Service_Line.sl_id )
GROUP BY
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY')

Same join using the ANSI 92 standard

SELECT
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY'),
sum(Invoice_Line.days * Invoice_Line.nb_guests * Ser
vice.price)
FROM
Resort INNER JOIN Service_Line ON (Resort.resort_id=Ser
vice_Line.resort_id)
INNER JOIN Service ON (Service.sl_id=Service_Line.sl_id)
INNER JOIN Invoice_Line ON (Invoice_Line.service_id=Ser
vice.service_id)
INNER JOIN Sales ON (Sales.inv_id=Invoice_Line.inv_id)
GROUP BY
Resort.resort,
'FY'+Format(Sales.invoice_date,'YYYY')

Powered by ComboStrap