ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Editors / Query editor / SQL
  • Overview
  • Inner joins
  • Definition
  • Use format
  • Example:
  • External joins
  • Definition
  • The different types of outer joins
  • Use format
  • Example 1:
  • Example 2:
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
To select records coming from several data files, 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.
Remark: These joins can be used:
The joins cannot be nested.
See a documentation specific to the SQL language for more details.
To discover all the SQL commands (functions, clauses, operators, etc.) that can be used in an SQL query managed by HFSQL, see Commands that can be used in an SQL query managed by HFSQL.
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

Several 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): syntax compatible with most databases: SQL Server, Access, MySQL and Oracle (except for the very old Oracle versions).
    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:

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 differs 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 JOINUsed 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 JOINMost 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 JOINUsed 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 1:

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

Example 2:

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
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help