Join - comparing default join syntax and ANSI 92 syntax

1 - 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.

3 - Examples

3.1 - 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')

3.2 - 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')


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap