SQL: Managing the joins (INNER JOIN, OUTER JOIN))

Overview

To select some records coming from several data files, some joins must be used in the SQL query.

Two types of joins can be used in the queries in SQL code:

• the inner joins.
• the outer joins.

Note: These joins can be used:

• in the SQL code of the queries created in the query editor and run by HExecuteQuery.
• in the SQL code of the queries run by HExecuteSQLQuery.

The joins cannot be nested.

See a documentation specific to the SQL language for more details.

To find out all the SQL commands (functions, clauses, operators, and so on) that can be used in a SQL query manage by HyperFileSQL, see Commands that can be used in a SQL query managed by HyperFileSQL.

Inner joins

Definition

An inner join is used to select the records that have correspondence between two joined files.

For example, to list all the customers who have placed orders, the "Customer" file must be linked to the "Orders" file via an inner join. The query will select the customers linked to at least one order number. The customers wo have placed no order will not be listed.

Use format

Three syntaxes are available:

• Syntax 1: Correspondence between identical items found in different files (using WHERE)

SELECT ...
FROM File1, File2, File3
WHERE File1.ItemName1 = File2.ItemName1
File2.ItemName2 = File3.ItemName2

• Syntax 2: Correspondence between identical items found in different files (using INNER JOIN)

SELECT ...
FROM File1INNER JOIN File2
ON (File1.NameItem1 = File2.NameItem1),
File2 INNER JOIN File3
ON (File2.NameItem2 = File3.NameItem2)

• Syntax 3: Correspondence between identical items found in different files (using INNER JOIN): syntax compatible with SQL Server, Access and MySQL.

SELECT ...
FROM (File1 INNER JOIN File2
ON File1.NameItem1 = File2.NameItem1)
INNER JOIN File3
ON File2.NameItem2 = File3.NameItem2

You also have the ability to establish links between several files on different items.

Example: the following SQL codes are used to select the customers who have placed orders:

SELECT CUSTOMER.CustomerLastName,
ORDERS.OrdNum
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum

equivalent to:

SELECT CUSTOMER.CustomerLastName,
ORDERS.OrdNum
FROM CUSTOMER INNER JOIN ORDERS
ON (CUSTOMER.CustNum = ORDERS.CustNum)

External joins

Definition

An outer join is used to select both:

• the records that have correspondence between the two linked files.
• the records that have no correspondence in the first file, in the second file or in all the linked files.

For example, to find out the total amount spent by each customer:

• By using an inner join, only the customers who have already placed an order (whose amount spent is different from 0) will be selected.
• By using an outer join, all the customers will be selected, even the ones who have never placed an order.

The different types of outer joins

FULL OUTER JOIN	Used to select: the records that fulfill the join. the records of the first listed file that do not fulfill the join. The name of the first listed file is found to the left of the OUTER JOIN. the records of the second listed file that do not fulfill the join. The name of the second listed file is found to the right of the OUTER JOIN.
LEFT OUTER JOIN	Most common join. Used to select: the records that fulfill the join. the records of the first listed file that do not fulfill the join. The name of the first listed file is found to the left of the OUTER JOIN.
RIGHT OUTER JOIN	Used to select: the records that fulfill the join. the records of the second listed file that do not fulfill the join. The name of the second listed file is found to the right of the OUTER JOIN.

Use format

The available syntaxes are as follows:

• Syntax 1: Correspondence between identical items found in different files

SELECT ...
FROM (File1 LEFT/RIGHT/FULL JOIN File2
ON File1.NameItem1 = File2.NameItem1)
LEFT/RIGHT/FULL JOIN File3
ON File2.NameItem2 = File3.NameItem2

• Syntax 2: Correspondence between identical items found in different files: syntax compatible with SQL Server, Access and MySQL.

SELECT ...
FROM File1LEFT/RIGHT/FULL JOIN File2
ON File1.NameItem1 = File2.NameItem1,

LEFT/RIGHT/FULL JOIN File3
ON File2.NameItem2 = File3.NameItem2

You also have the ability to establish links between several files on different items.

Example: The following SQL code is used to list the total amount spent by each customer. Even the customers who have never placed an order will be selected.

SELECT CUSTOMER.CustomerLastName,
SUM(ORDERS.TotalIOT) AS TotalIOT
FROM CUSTOMER LEFT OUTER JOIN ORDERS
ON (CUSTOMER.CustNum = ORDERS.CustNum)
GROUP BY CUSTOMER.CustomerLName

Example: The following SQL code is used to list the number of products ordered by each customer who has placed at least one order.

SELECT CUSTOMER.CustNum,
COUNT(DISTINCT PRODUCT.Reference) AS NbProducts
FROM CUSTOMER LEFT OUTER JOIN ORDERS
ON CUSTOMER.CustNum = ORDERS.CustNum,
ORDERS JOIN ORDLINE
ON ORDERS.OrdNum = ORDLINE.OrdNum,
ORDLINE JOIN PRODUCT
ON ORDLINE.Reference = PRODUCT.Reference
GROUP BY CUSTOMER.CustNum, PRODUCT.Reference

Equivalent to:

SELECT CUSTOMER.CustomerLastName,
COUNT(DISTINCT PRODUCT.Reference) AS NbProducts
FROM CUSTOMER, ORDERS, ORDLINE, PRODUCT
WHERE CUSTOMER.CustNum = ORDERS.CustNum(+)
AND ORDERS.OrdNum = ORDLINE.OrdNum
AND ORDLINE.Reference = PRODUCT.Reference
GROUP BY CUSTOMER.CustNum, PRODUCT.Reference

Also equivalent to:

SELECT CUSTOMER.CustNum,
COUNT(DISTINCT PRODUCT.Reference) AS NbProducts
FROM ((CUSTOMER LEFT OUTER JOIN ORDERS
ON CUSTOMER.CustNum = ORDERS.CustNum)
JOIN ORDLINE
ON ORDERS.OrdNum = ORDLINE.OrdNum)
JOIN PRODUCT
ON ORDLINE.Reference = PRODUCT.Reference
GROUP BY CUSTOMER.CustNum, PRODUCT.Reference