Download Nurture Join, Left Outer Join, Right Outer Join - Notes | DS 3860 and more Study notes Introduction to Database Management Systems in PDF only on Docsity!
September 4, 2015 Join: Add the rows of one table to the rows of another table. Natural Join: Must have at least one column in common, and the values must be drawn from the same domain. ***Do not duplicate the column in the joined table. Students Table: SID Name FID 100 Adam FSW 200 Baker CPA 300 Charles CPA 400 David CM 500 Edward RR Faculty Table: FID LName FSW Wells CPA Armstrong CM Miller BJ Jones ***RESULT OF NATURAL JOIN: SID Name FID LName 100 Adam FSW Wells 200 Baker CPA Armstrong 300 Charles CPA Armstrong 400 David CM Miller Equi-Join: Same as a natural join, but an equi-join has a duplicate column, even if the column names are different. ***This will be on the test.
Left-Outer Join: Take all rows from the table on the left, and join it with the appropriate rows on the right. You may have rows that don’t have matches. Simply fill missing rows with nulls (nothing). Students Table: SID Name FID 100 Adam FSW 200 Baker CPA 300 Charles CPA 400 David CM 500 Edward RR Faculty Table: FID LName FSW Wells CPA Armstrong CM Miller BJ Jones ***Solution to Left-Outer Join: SID Name FID LName 100 Adam FSW Wells 200 Baker CPA Armstrong 300 Charles CPA Armstrong 400 David CM Miller 500 Edward RR
Theta Join: Theta means anything not equal to (<,>). Opposite of Equi-Join. Students Table: FacultyTable: SID Name FID 10 0 Adam FS W 30 0 Charles CPA 40 0 David CM 50 0 Edwar d
RR
***Solution to Theta Join SID Name FID FID LName 10 0 Adam FS W CPA Armstron g 10 0 Adam FS W BJ Jones 30 0 Charles CPA FS W Wells 30 0 Charles CPA BJ Jones 40 0 David CM FS W Wells 40 0 David CM BJ Jones 50 0 Edwar d
RR FS
W
Wells 50 0 Edwar d RR CPA Armstron g 50 0 Edwar d RR BJ Jones 50 0 Edwar d Table Divided by another Table: Find the column that has all attributes of the right table, use that column header and the output is the values that satisfy the requirements of the right table. Maintenance Table: Repair Table: Car Repair 10 0 Tire 10 Engine FID LName FSW Wells CPA Armstrong BJ Jones
Engine 30 0 Tire 30 0 Lightbul b 10 0 Lightbul b 40 0 Door ***Solution to Divide Table by Table: Car 100 300 Repair Tire Lightbulb
