“When spider webs unite they can tie up a lion.”
- African Proverb
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 is which Column from each table is a match. In this case, Customer_Number is a match that establishes the relationship, so this join will happen on matching Customer_Number columns.
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 which Column from each table is a match. In this case, Customer_Number is a match that establishes the relationship.
SELECT
Customer_Number
,Customer_Name
,Order_Number
,Order_Total
FROM Customer_Table
INNER JOIN
Order_Table
Using (Customer_Number) ;
The example above uses the keyword INNER JOIN (or just JOIN) and then the USING clause to define the column from both table that establishes the join relationship. The Customer_Number from both tables represents that condition.
Whenever a column is in both tables, you must fully qualify it when doing a join. You don’t have to fully qualify tables that are only in one of the tables because the system knows which table that particular column is in. You can choose to fully qualify every column if you like. This is a good practice because it is more apparent which columns belong to which tables for anyone else looking at your SQL.
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. Rows are joined when the Customer_Number matches on both tables, but non-matches won’t return.
Both of these syntax techniques bring back the same result set and have the same performance. The INNER JOIN is considered ANSI. Which one does Outer Joins?
SELECT First_Name, Last_Name,
Department_Name
FROM Employee_Table E
INNER JOIN
Department_Table D
ON
Finish the Join
Finish this join by placing the missing SQL in the proper place!
This query is ready to run.
This query has an error! Can you find it?
If a column in the SELECT list is in both tables, you must fully qualify it.
This query has an error! Can you find it?
If a column in the SELECT list is in both tables, you must fully qualify it.
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!
The bottom line is that the three rows excluded did not have a matching Dept_No.
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.
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!
-- Oracle Left Outer Join
-- with Employee_Table as the Outer Table
SELECT E.First_Name
,D.Department_Name
FROM Employee_Table E
,Department_Table D
WHERE E.Dept_No = D.Dept_No (+) ;
Oracle allows you to use the traditional join syntax with a (+) sign to create an outer join. Notice that the (+) sign in the example above is next to the Department_Table’s D.Dept_No column. This makes the Employee_Table the outer table.
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.
All rows from the Right Table were returned with matches and since Dept_No 500 didn’t have a match, the system put a NULL Value for Column values from the Left Table.
-- Oracle Right Outer Join
-- with Department_Table as the Outer Table
SELECT E.First_Name
,D.Department_Name
FROM Employee_Table E
,Department_Table D
WHERE E.Dept_No (+) = D.Dept_No ;
Oracle allows you to use the traditional join syntax with a (+) sign to create an outer join. Notice that the (+) sign in the example above is next to the Employee_Table’s E.Dept_No column. This makes the Department_Table the outer table.
This 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!
Can you list which tables above are left tables and which tables are right tables?
The first table is always the left table and the rest are right tables. The results from the first two tables being joined becomes the left table.
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 rows that don't qualify.
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.
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.
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.
The additional AND is performed in conjunction with the ON statement on Outer Joins. All rows will be evaluated with the ON clause and the AND combined.
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, so she showed up like expected, but an outer join returns non-matches also. Ouch!!!
This is considered an INNER JOIN because we are doing a LEFT OUTER JOIN on the Employee_Table and then filtering with the WHERE for a column in the right table!
SELECT Cou.*, STU1.*
FROM COURSE_TABLE Cou
LEFT OUTER JOIN
STUDENT_COURSE_TABLE STU
ON Cou.Course_Id = STU.Course_Id
LEFT OUTER JOIN STUDENT_TABLE STU1
ON STU.Student_Id = STU1.Student_Id;
When you perform an inner join, Oracle considers this to be both commutative and associative. This means that two tables being inner joined will easily come up with the intended answer. This allows the optimizer to select the best join order between tables. This is because the end result will be the same. Outer Joins are different. They will follow the above three rules for evaluation order by the Parsing Engine.
The query above becomes a Product Join because it does not possess any JOIN Conditions (Join Keys). Every row from one table is compared to every row from the other table, and quite often, the data is not what you intended to get back.
A Product Join is often a mistake! Two department rows had an ‘m’ in their name, so these were joined to every employee, and the information is worthless.
A Cartesian Product Join is usually a big mistake. 45 rows came back in the answer set. 9 rows from the first table times 5 rows from the second table equals 45 rows.
This causes an error. ANSI won’t let this run unless a join condition is present.
Do these two queries produce the same result?
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!
This query becomes a Product Join because a Cross Join is an ANSI Product Join. It will compare every row from the Customer_Table to Order_Number 123456 in the Order_Table. Check out the Answer Set on the next page.
This Cross Join produces information that compares every row with every row.
A Self Join gives itself 2 different Aliases, which is then seen as two different tables.
A Self Join gives itself 2 different Aliases, which is then seen as two different tables.
Will both queries bring back the same result set?
Will both queries bring back the same result set? Yes! Because they’re both inner joins.
Will both queries bring back the same result set?
Will both queries bring back the same result set? NO! The WHERE is performed last.
How would you join these two tables together? You can't do it. There is no matching column with like data. There is no Primary Key/Foreign Key relationship between these two tables. That is why you are about to be introduced to a bridge table. It is formally called an Associative table or a Lookup table.
The Associative Table is a bridge between the Course_Table and Student_Table.
SELECT ALL Columns from the Course_Table and Student_Table and Join them.
The Associative Table is a bridge between the Course_Table and Student_Table, and its sole purpose is to join these two tables together.
SELECT S.*, C.*
FROMStudent_Table S,
Course_Table C,
Student_Course_Table SC
Where S.Student_ID = SC.Student_ID
ANDC.Course_ID = SC.Course_ID ;
Convert this query to ANSIsyntax
Please re-write the above query using ANSI Syntax.
The above queries show both traditional and ANSI form for this three table join.
Please re-write the above query and place both 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.
Above is the logical model for the insurance tables showing the Primary Key and Foreign Key relationships (PK/FK).
Your mission is to write a five table join selecting all columns using ANSI syntax.
SELECT
cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROMCLAIMS cla1
INNER JOIN
SUBSCRIBERS sub1
ONcla1.Subscriber_No = sub1.Subscriber_No
ANDcla1.Member_No = sub1.Member_No
INNER JOIN
ADDRESSES add1
ONsub1.Subscriber_No = add1.Subscriber_No
INNER JOIN
PROVIDERS pro1
ONcla1.Provider_No = pro1.Provider_Code
INNER JOIN
SERVICES ser1
ONcla1.Claim_Service = ser1.Service_Code ;
Above is the example writing this five table join using ANSI syntax.
Your mission is to write a five table join selecting all columns using Non-ANSI syntax.
SELECTcla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROMCLAIMS cla1,
SUBSCRIBERS sub1,
ADDRESSES add1,
PROVIDERS pro1,
SERVICES ser1
WHEREcla1.Subscriber_No = sub1.Subscriber_No
ANDcla1.Member_No = sub1.Member_No
ANDsub1.Subscriber_No = add1.Subscriber_No
ANDcla1.Provider_No = pro1.Provider_Code
ANDcla1.Claim_Service = ser1.Service_Code ;
Above is the example writing this five table join using Non-ANSI syntax.
SELECT
cla1.*, sub1.*, add1.* ,pro1.*, ser1.*
FROMCLAIMS cla1
INNER JOIN
SUBSCRIBERS sub1
ONcla1.Subscriber_No = sub1.Subscriber_No
ANDcla1.Member_No = sub1.Member_No
INNER JOIN
ADDRESSES add1
ONsub1.Subscriber_No = add1.Subscriber_No
INNER JOIN
PROVIDERS pro1
ONcla1.Provider_No = pro1.Provider_Code
INNER JOIN
SERVICES ser1
ONcla1.Claim_Service = ser1.Service_Code ;
Above is the example writing this five table join using Non-ANSI syntax.
Above is the example writing this five table join using ANSI syntax with the ON clauses at the end. We also had to move the tables around to make this happen. Notice that the first ON clause represents the last two tables being joined, and then it works backwards.
Let Nexus show users the table relationships and then let Nexus build the SQL. Just load the ERwin logical model inside Nexus and then all users can point and click.