[go: up one dir, main page]
Scribd
Upload
145 views10 pages

4d SQL Join

This document discusses how to join multiple tables in a database using SQL. It explains that to display columns from different tables, a join operation is required rather than a subquery. Two tables can be joined together by matching the primary key of one table with the foreign key of another. The document provides examples of SQL queries that join two or more tables to retrieve employee names, hours worked, project names, and other fields from multiple tables based on key matches. It also covers using table aliases and the INNER JOIN syntax.

Uploaded by

Noah Lawhead
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
145 views10 pages

4d SQL Join

This document discusses how to join multiple tables in a database using SQL. It explains that to display columns from different tables, a join operation is required rather than a subquery. Two tables can be joined together by matching the primary key of one table with the foreign key of another. The document provides examples of SQL queries that join two or more tables to retrieve employee names, hours worked, project names, and other fields from multiple tables based on key matches. It also covers using table aliases and the INNER JOIN syntax.

Uploaded by

Noah Lawhead
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 10

CSCI N207 Data Analysis with Spreadsheets

4d. Structured Query


Language JOIN Operation

Lingma Acheson
Department of Computer and Information Science
IUPUI

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 wont 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.
SELECT FirstName, LastName, HoursWorked
FROM EMPLOYEE, ASSIGNMENT
WHERE EMPLOYEE.EmployeeNumber =
ASSIGNMENT.EmployeeNumber;

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.
SELECT EMPLOYEE.EmployeeNumber, FirstName,
LastName, HoursWorked
FROM EMPLOYEE, ASSIGNMENT
WHERE EMPLOYEE.EmployeeNumber =
ASSIGNMENT.EmployeeNumber;
3

Joining Multiple Tables


Notice that the records that dont match in two
tables wont 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.
SELECT EMPLOYEE.EmployeeNumber, FirstName,
LastName, HoursWorked
FROM EMPLOYEE, ASSIGNMENT
WHERE EMPLOYEE.EmployeeNumber =
ASSIGNMENT.EmployeeNumber AND
Department = Accounting;
4

Joining Multiple Tables


E.g. Show the names of the employees and their
hours worked but only interested in hours
worked more than 45.
SELECT EMPLOYEE.EmployeeNumber, FirstName,
LastName, HoursWorked
FROM EMPLOYEE, ASSIGNMENT
WHERE EMPLOYEE.EmployeeNumber =
ASSIGNMENT.EmployeeNumber AND
HoursWorked > 45;

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)

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.
SELECT E.EmployeeNumber, FirstName, LastName,
HoursWorked
FROM EMPLOYEE AS E, ASSIGNMENT AS A
WHERE E.EmployeeNumber = A.EmployeeNumber
AND Department = Accounting;
7

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.
SELECT P.ProjectName, FirstName, LastName,
HoursWorked
FROM EMPLOYEE AS E, ASSIGNMENT AS A,
PROJECT AS P
WHERE E.EmployeeNumber = A.EmployeeNumber
AND P.ProjectID = A.ProjectID

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:
SELECT FirstName, LastName, HoursWorked
FROM EMPLOYEE AS E, ASSIGNMENT AS A
WHERE E.EmployeeNumber = A.EmployeeNumber;

Using the INNER JOIN key word:


SELECT FirstName, LastName, HoursWorked
FROM EMPLOYEE AS E INNER JOIN ASSIGNMENT AS A ON
E.EmployeeNumber = A.EmployeeNumber;
9

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.
Its 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.
Query Saved - ProjectAndAssignment:
SELECT *
FROM PROJECT AS A INNER JOIN ASSIGNMENT AS A ON
P.ProjectID = A.ProjectID;
Query - ProjectAndHoursAccounting
SELECT ProjectName, HoursWorked
FROM ProjectAndAssignment
WHERE Department = Accounting;

10

You might also like