Join - comparing default join syntax and ANSI 92 syntax

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

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