4d. Structured Query Language – JOIN Operation

1. CSCI N207 Data Analysis with Spreadsheets 4d. Structured Query Language – JOIN Operation Lingma Acheson Department of Computer and Information Science IUPUI

2. Joining Multiple Tables • What if we also want to see the Hours Worked for the previous query? • If we need to display columns from different tables, subqueries won’t work. Must use a Join Operation. • Two tables can be joined together to form a large table based on the matching of the primary key and the foreign key. • E.g. Show the names of all the employees and the hours worked. SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber;

3. Joining Multiple Tables • Join Operation – • Must list all the tables in the FROM clause. • Must indicate which field in what table is matched with which field in what table. • Duplicate field names from different tables must be prefixed by their table names using the dot notation. • E.g. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber;

4. Joining Multiple Tables • Notice that the records that don’t match in two tables won’t show in the result. • Can apply other WHERE conditions. • E.g. Show the names of the employees and their hours worked from the Accounting department. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber AND Department = ‘Accounting’;

5. Joining Multiple Tables • E.g. Show the names of the employees and their hours worked but only interested in hours worked more than 45. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber AND HoursWorked > 45;

6. Joining Multiple Tables • Practice: • Show projects whose maximum hours are smaller than 140. Need to see all the project information, as well as the budget code for departments. • An email list of employees, need to see their names, emails, department name, and department phone number. Sorted by last name. • How many total hours have been worked on a certain project? Show the project name, and the total hours. (hint: a combination of SUM, WHERE and GROUP BY)

7. Joining Multiple Tables • Using table aliases to avoid repeatedly typing long table names – in the FROM clause, use AS XX after a table name with XX as the new shortened name for that table. • E.g. Show the names of the employees and their hours worked from the Accounting department. SELECTE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE AS E, ASSIGNMENT AS A WHEREE.EmployeeNumber =A.EmployeeNumber AND Department = ‘Accounting’;

8. Joining Multiple Tables • Can join more than two tables. • E.g. Show the names of the employees and their hours worked on each project, including the project name. SELECTP.ProjectName, FirstName, LastName, HoursWorked FROM EMPLOYEE AS E, ASSIGNMENT AS A, PROJECT AS P WHEREE.EmployeeNumber =A.EmployeeNumber AND P.ProjectID = A.ProjectID

9. Joining Multiple Tables • Using the JOIN … ON key words • ACCESS syntax: must use INNER JOIN … ON • E.g. Show the names of all the employees and the hours worked. Without using the INNERJOIN key word: SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE AS E,ASSIGNMENT AS A WHEREE.EmployeeNumber= A.EmployeeNumber; Using the INNER JOIN key word: SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE AS E INNER JOIN ASSIGNMENT AS A ON E.EmployeeNumber= A.EmployeeNumber;

10. Joining Multiple Tables • Often use the graphic interface to add the tables to be joined. The table will be joined automatically based on the relationships defined. • It’s handy to create and save a query that shows all the joined data and later create queries based on this grand query, thus no need to join all the time. E.g. QuerySaved - ProjectAndAssignment:SELECT * FROM PROJECT AS A INNER JOIN ASSIGNMENT AS A ONP.ProjectID = A.ProjectID; Query - ProjectAndHoursAccounting SELECT ProjectName, HoursWorked FROM ProjectAndAssignment WHERE Department = ‘Accounting’;