Join - comparing default join syntax and ANSI 92 syntax =

Data System Architecture

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





Discover More
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...



Share this page:
Follow us:
Task Runner