- Overview
- Inner joins
- Definition
- Use format
- Example:
- External joins
- Definition
- The different types of outer joins
- Use format
- Example 1:
- Example 2:
SQL: Managing joins (INNER JOIN, OUTER JOIN)
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. 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)
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 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 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
This page is also available for…
|
|
|
|