Chapter 18
Join Functions
“When spider webs unite they can tie up a lion”
Table of Contents Chapter 18 – Join Functions
– A two-table join using Non-ANSI Syntax
– A two-table join using Non-ANSI Syntax with Table Alias
– Aliases and Fully Qualifying Columns
– A two-table join using ANSI Syntax
– Both Queries have the same Results and Performance
– Quiz – Can You Finish the Join Syntax?
– Answer to Quiz – Can You Finish the Join Syntax?
– Quiz – Can You Find the Error?
– Answer to Quiz – Can You Find the Error?
– Quiz – Which rows from both tables Won't Return?
– Answer to Quiz – Which rows from both tables Won't Return?
– INNER JOIN with Additional AND Clause
– ANSI INNER JOIN with Additional AND Clause
– ANSI INNER JOIN with Additional WHERE Clause
– OUTER JOIN with Additional AND Clause
– OUTER JOIN with Additional WHERE Clause
– OUTER JOIN with Additional AND Clause
– OUTER JOIN with Additional AND Clause Example
– Quiz – Why is this Considered an INNER JOIN?
– The Horrifying Cartesian Product Join
– The ANSI Cartesian Join will ERROR
– Quiz – Do these Joins Return the Same Answer Set?
– Answer – Do these Joins Return the Same Answer Set?
– The Self Join with ANSI Syntax
– Quiz – Will both queries bring back the same Answer Set?
– Answer – Will both queries bring back the same Answer Set?
– Quiz – Will both queries bring back the same Answer Set?
– Answer – Will both queries bring back the same Answer Set?
– How would you Join these two tables?
– How would you Join these two tables? You Can't….Yet!
– An Associative Table is a Bridge that Joins Two Tables
– Quiz – Can you Write the 3-Table Join?
– Answer to Quiz – Can you Write the 3-Table Join?
– Quiz – Can you Write the 3-Table Join to ANSI Syntax?
– Answer – Can you Write the 3-Table Join to ANSI Syntax?
– Quiz – Can you Place the ON Clauses at the End?
– Answer – Can you Place the ON Clauses at the End?
– The 5-Table Join – Logical Insurance Model
– The 5-Table Join ANSI SQL Created by Nexus
– The 5-Table Join With ON Clauses at END
A two-table join using Non-ANSI Syntax
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
SELECT Customer_Table.Customer_Number, Customer_Name,
Order_Number, Order_Total
FROM Customer_Table,
Order_Table
WHERE Customer_Table.Customer_Number = Order_Table.Customer_Number ;
A Join combines columns on the report from more than one table. The example above joins the Customer_Table and the Order_Table together. The most complicated part of any join is the JOIN CONDITION. The JOIN CONDITION means what Column from each table is a match. In this case Customer_Number is a match that establishes the relationship.
A two-table join using Non-ANSI Syntax with Table Alias
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
SELECT Cust.Customer_Number, Customer_Name,
Order_Number, Order_Total
FROM Customer_Table as Cust
Order_Table as ORD
WHERE Cust.Customer_Number = Ord.Customer_Number ;
A Join combines columns on the report from more than one table. The example above joins the Customer_Table and the Order_Table together. The most complicated part of any join is the JOIN CONDITION. The JOIN CONDITION means what Column from each table is a match. In this case Customer_Number is a match that establishes the relationship.
Aliases and Fully Qualifying Columns
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
Customer_Number is a column in both the Customer and Order Tables. CUST.Customer_Number fully qualifies the column to specifically state we want the Customer_Number from the Customer_Table. That is why we ALIASED the table names, so we could fully qualify any columns in both tables, or we error!
A two-table join using ANSI Syntax
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
This is the same join as the previous slide except it is using ANSI syntax. Both will return the same rows with the same performance. Row are joined when the Customer_Number matches on both tables, but non-matches won't return.
Both Queries have the same Results and Performance
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
/* Traditional Syntax */
SELECT Cust.Customer_Number,
Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust,
Order_Table as ORD
WHERE Cust.Customer_Number
= Ord.Customer_Number ;
/* ANSI Syntax */
SELECT Cust.Customer_Number,
Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
INNER JOIN
Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number ;
Both of these syntax techniques bring back the same result set and have the same performance. The INNER JOIN is considered t ANSI. Which one does Outer Joins?
Quiz – Can You Finish the Join Syntax?
SELECT First_Name, Last_Name,
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON
Finish this join by placing the missing SQL in the proper place!
Answer to Quiz – Can You Finish the Join Syntax?
This query is ready to run. Will all rows return or will some rows not return? NO!
Quiz – Can You Find the Error?
SELECT First_Name, Last_Name, Dept_No
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No ;
This query has an error! Can you find it?
Answer to Quiz – Can You Find the Error?
If a column in the SELECT list is in both tables you must fully qualify it.
Quiz – Which rows from both tables Won't Return?
SELECT First_Name, Last_Name,
Department_Name
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No ;
An Inner Join returns matching rows, but did you know an Outer Join returns both matching rows and non-matching rows? You will understand soon!
Answer to Quiz – Which rows from both tables Won't Return?
Squiggy Jones has a NULL Dept_No
Richard Smythe has an invalid Dept_No 10
No Employees work in Department 500
The bottom line is the three rows excluded did not have a matching Dept_No.
LEFT OUTER JOIN
This is a LEFT OUTER JOIN. That means that all rows from the LEFT Table will appear in the report regardless if it finds a match on the right table.
Left OUTER JOIN
SELECT First_Name, Department_Name
FROM Employee_Table as E
LEFT OUTER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No ;
A LEFT Outer Join Returns all rows from the LEFT Table, including all Matches. If a LEFT row can't find a match a NULL is placed on right columns not found!
RIGHT OUTER JOIN
This is a RIGHT OUTER JOIN. That means that all rows from the RIGHT Table will appear in the report regardless if it finds a match with the LEFT Table.
RIGHT OUTER JOIN
SELECT First_Name, Department_Name
FROM Employee_Table as E
RIGHT OUTER JOIN
Department_Table as D
ON E.DeptyNo = D.Dept_No ;
All rows from the Right Table were returned with matches and Dept_No 500 didn't have a match so they system put a NULL Value for Left Column values.
FULL OUTER JOIN
The is a FULL OUTER JOIN. That means that all rows from both the RIGHT and LEFT Table will appear in the report regardless if it finds a match.
The FULL Outer Join Returns all rows from both Tables. NULLs show the flaws!
INNER JOIN with Additional AND Clause
The additional AND is performed first in order to eliminate unwanted data so the join is less intensive than joining everything first and then eliminating.
ANSI INNER JOIN with Additional AND Clause
The additional AND is performed first in order to eliminate unwanted data so the join is less intensive than joining everything first and then eliminating after.
ANSI INNER JOIN with Additional WHERE Clause
The additional WHERE is performed first in order to eliminate unwanted data so the join is less intensive than joining everything first and then eliminating.
OUTER JOIN with Additional AND Clause
The additional WHERE is performed last on Outer Joins. All rows will be joined first and then the additional WHERE clause filters.
OUTER JOIN with Additional WHERE Clause
The additional WHERE is performed last on Outer Joins. All rows will be joined first and then the additional WHERE clause filters after the join takes place.
OUTER JOIN with Additional AND Clause
The additional AND is performed in conjunction with the ON statement on Outer Joins. All rows will evaluated with the ON clause and the AND combined.
OUTER Join with additional AND
SELECT First_Name, Department_Name
FROM Employee_Table as E
LEFT OUTER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No
AND E.Dept_No = 100 ;
| First_Name | Department_Name |
| Mandee | Marketing |
| Herbert | ? |
| William | ? |
| Loraine | ? |
| Squiggy | ? |
| Richard | ? |
| Cletus | ? |
| Billy | ? |
| John | ? |
The additional AND is performed in conjunction with the ON statement on Outer Joins. This can surprise you. Only Mandee is in Dept_No 100!
Quiz – Why is this Considered an INNER JOIN?
This is considered an INNER JOIN because we are doing a LEFT OUTER JOIN on the Employee_Table and then filtering with the AND for a column in the right table! If this performed a RIGHT OUTER JOIN it would perform a RIGHT OUTER JOIN.
The DREADED Product Join
This query is becoming a Product Join because it does not possess any JOIN Conditions (Join Keys). Every row from one table is compared to every row of the other table and quite often the data is not what you intended to get back.
The DREADED Product Join
How can Billy Coffing work in 3 different departments?
A Product Join is often a mistake! 3 Department rows had an ‘m’ in their name so these were joined to every employee, and the information is worthless.
The Horrifying Cartesian Product Join
This joins every row from one table to every row of another table. 9 rows multiplied by 5 rows = 45 rows of complete nonsense!
A Cartesian Product Join is a big mistake.
The ANSI Cartesian Join will ERROR
This causes an error. ANSI won't let this run unless a join condition is present.
Quiz – Do these Joins Return the Same Answer Set?
/* Query 1*/
SELECT First_Name,
Department_Name
FROM Employee_Table
INNER JOIN
Department_Table ;
/* Query 2*/
SELECT First_Name,
Department_Name
FROM Employee_Table,
Department_Table ;
Do these two queries produce the same result?
Answer – Do these Joins Return the Same Answer Set?
/* Query 1*/
SELECT First_Name,
Department_Name
FROM Employee_Table
INNER JOIN
Department_Table ;
/* Query 2*/
SELECT First_Name,
Department_Name
FROM Employee_Table
Department_Table ;
Do these two queries produce the same result? No, Query 1 Errors due to ANSI syntax and no ON Clause, but Query 2 Product Joins to bring back junk!
The CROSS JOIN
This query is becoming a Product Join because it does not possess any JOIN.
The CROSS JOIN Answer Set
This Cross Join produces information that just isn't worth anything quite often!
The Self Join
SELECT Mgrs.Dept_No, Mgrs.Last_Name as MgrName, Mgrs.Salary as MgrSal,
Emps.Last_Name as EmpName, Emps.Salary as Empsal
FROM Employee_Table2 as Emps,
Employee_Table2 as Mgrs
WHERE Emps.Dept_No = Mgrs.Dept_No
AND Mgrs.Mgr = ‘Y’
AND Emps.Salary > Mgrs.Salary ;
Which Workers make a bigger Salary than their Manager?
A Self Join gives itself 2 different Aliases, which is then seen as two different tables.
The Self Join with ANSI Syntax
A Self Join gives itself 2 different Aliases, which is then seen as two different tables.
Quiz – Will both queries bring back the same Answer Set?
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
INNER JOIN Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
WHERE
Customer_Name like ‘Billy%’
ORDER BY 1;
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
INNER JOIN Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
AND
Customer_Name like ‘Billy%’
ORDER BY 1;
Will both queries bring back the same result set?
Answer – Will both queries bring back the same Answer Set?
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
INNER JOIN Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
WHERE
Customer_Name like ‘Billy%’
ORDER BY 1;
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
INNER JOIN Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
AND
Customer_Name like ‘Billy%’
ORDER BY 1;
Will both queries bring back the same result set? Yes! Because their both inner joins.
Quiz – Will both queries bring back the same Answer Set?
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
LEFT OUTER JOIN
Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
WHERE
Customer_Name like ‘Billy%’
ORDER BY 1;
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
LEFT OUTER JOIN
Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
AND
Customer_Name like ‘Billy%’
ORDER BY 1;
Will both queries bring back the same result set?
Answer – Will both queries bring back the same Answer Set?
| Customer_Table | |
| Customer_Number | Customer_Name |
| 11111111 | Billy's Best Choice |
| 31313131 | Acme Products |
| 31323134 | ACE Consulting |
| 57896883 | XYZ Plumbing |
| 87323456 | Databases N-U |
| Order_Table | ||
| Order_Number | Customer_Number | Order_Total |
| 123456 | 11111111 | 12347.53 |
| 123512 | 11111111 | 8005.91 |
| 123552 | 31323134 | 5111.47 |
| 123585 | 87323456 | 15231.62 |
| 123777 | 57896883 | 23454.84 |
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
LEFT OUTER JOIN
Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
WHERE
Customer_Name like ‘Billy%’
ORDER BY 1;
SELECT Customer_Name,
Order_Number,
Order_Total
FROM Customer_Table as Cust
LEFT OUTER JOIN
Order_Table as ORD
ON Cust.Customer_Number
= Ord.Customer_Number
AND
Customer_Name like ‘Billy%’
ORDER BY 1;
Will both queries bring back the same result set? NO! The WHERE is performed last.
How would you Join these two tables?
Looking at the columns above which will allow these two tables to join?
How would you Join these two tables? You Can't….Yet!
Get ready for the Associative Table!
An Associative Table is a Bridge that Joins Two Tables
The Associative Table is a bridge between the Course_Table and Student_Table.
Quiz – Can you Write the 3-Table Join?
SELECT ALL Columns from the Course_Table and Student_Table and Join them.
Answer to Quiz – Can you Write the 3-Table Join?
The Associative Table is a bridge between the Course_Table and Student_Table.
Quiz – Can you Write the 3-Table Join to ANSI Syntax?
Please re-write the above query using ANSI Syntax.
Answer – Can you Write the 3-Table Join to ANSI Syntax?
Select S.*, C.*
From Student_Table as S
INNER JOIN?
Student_Course_Table as SC
ON S.Student_ID = SC.Student_ID
INNER JOIN
Course_Table as C
ON C.Course_ID = SC.Course_ID;
The above query has been writing using ANSI Syntax.
Quiz – Can you Place the ON Clauses at the End?
Select S.*, C.*
From Student_Table as S
INNER JOIN?
Student_Course_Table as SC
ON S.Student_ID = SC.Student_ID
INNER JOIN
Course_Table as C
ON C.Course_ID = SC.Course_ID;
Please re-write the above query and place both ON Clauses at the end.
Answer – Can you Place the ON Clauses at the End?
This is tricky. The only way it works is to place the ON clauses backwards. The first ON Clause represents the last INNER JOIN, and then moves backwards.
The 5-Table Join – Logical Insurance Model
The 5-Table Join – Logical Insurance Model
The Nexus Query Chameleon
The 5-Table Join ANSI SQL Created by Nexus
SELECT
cla1.Claim_Id, cla1.Claim_Date, cla1.Subscriber_No, cla1.Member_No,
cla1.Claim_Amt, cla1.Provider_No, cla1.Claim_Service,
sub1.Last_Name, sub1.First_Name, sub1.Gender,
sub1.SSN, sub1.Member_No, sub1.Subscriber_No,
add1.Street, add1.City, add1.State, add1.Zip, add1.AreaCode,
add1.Phone, add1.Subscriber_No,
pro1.Provider_Code, pro1.Provider_Name, pro1.P_Address,
pro1.P_City, pro1.P_State,pro1.P_Zip,pro1.P_Error_Rate,
ser1.Service_Code,ser1.Service_Desc,ser1.Service_Pay
FROM SQL_CLASS.CLAIMS AS cla1
INNER JOIN SQL_CLASS.SUBSCRIBERS AS sub1
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
INNER JOIN SQL_CLASS.ADDRESSES AS add1
ON sub1.Subscriber_No = add1.Subscriber_No
INNER JOIN SQL_CLASS.PROVIDERS AS pro1
ON cla1.Provider_No = pro1.Provider_Code
INNER JOIN SQL_CLASS.SERVICES AS ser1
ON cla1.Claim_Service = ser1.Service_Code;
The 5-Table Join With ON Clauses at END
The Join Tab of Nexus
SELECT
cla1.*, sub1.*, add1.*, pro1.*, ser1.*
FROM SQL_CLASS.CLAIMS AS cla1
INNER JOIN SQL_CLASS.SUBSCRIBERS AS sub1
ON cla1.Subscriber_No = sub1.Subscriber_No
AND cla1.Member_No = sub1.Member_No
INNER JOIN SQL_CLASS.ADDRESSES AS add1
ON sub1.Subscriber_No = add1.Subscriber_No
INNER JOIN SQL_CLASS.PROVIDERS AS pro1
ON cla1.Provider_No = pro1.Provider_Code
INNER JOIN SQL_CLASS.SERVICES AS ser1
ON cla1.Claim_Service = ser1.Service_Code;
The COLUMNS Tab of Nexus
SELECT SQL
