zeit2301 design of information systems multi-table queries in sql school of engineering and...

ZEIT2301Design of Information Systems

Multi-Table Queries in SQL

School of Engineering and Information TechnologyUNSW@ADFA

Dr Kathryn Merrick

Topic 12: Multi-Table Queries in SQL

Today’s lecture will look at how to write SQL statements for multi-table queries: Inner (Natural) Joins Outer Joins Unions

References: http://www.w3schools.com/sql/sql_join.asp Connolly & Begg, Database Solutions, Pearson, 2004.

Combining Tables

Very often, in order to answer a query, it is necessary to combine data from different tables – that is, it is necessary to perform a JOIN

This can be done using the SQL SELECT statement by: listing the tables to be joined after the FROM keyword, and specifying how they are to be joined using the WHERE clause

Joining Tables in SQL

Consider the database design:

student(studentNo, Name, DOB, Address, Gender, Degree)

enrolIn(studentNo, courseCode, Mark)

course(courseCode, courseName, Lecturer)

lecturer(Name, Department, Telephone, Title)

Joining Tables in SQL

Now, how can we find out which Course (i.e. the courseCode) student Jane Doe is enrolled in?

The data we need is spread across two tables: student (has student name) and

enrolIn (has course code).

We need a JOIN of the student and enrolIn tables.

JOINs are undertaken typically via the Primary Key/Foreign Key links between tables.

(In this example, the PK/FK link is studentNo).

Your Relational Schema helps you to see these PK/FK links

Join Conditions

The query is:SELECT Name, courseCode

FROM student, enrolIn

WHERE student.studentNo = enrolIn.studentNo AND Name = ‘Jane Doe’;

The condition in italics is the JOIN condition – it determines how the two tables are linked together

student(studentNo, Name, DOB, Address, Gender, Degree)enrolIn(studentNo, courseCode, Mark)

Note: Both tables listed in FROM clause

Referencing Attributes

If two attributes in different tables have the same name then these need to be distinguished in an SQL query by attaching the relevant table name to the attribute (i.e. tableName.attributeName)

Example: lecturer.Name student.Name

student(studentNo, Name, DOB, Address, Gender, Degree)lecturer(Name, Department, Telephone, Title)

Table Name Aliases

Table name aliases can be used for table names (or column names) to simplify statements and give tables temporary names

Aliasing a table name:FROM lecturer AS L or simplyFROM lecturer L

“L” can then be used in place of “Lecturer” eg where L.name LIKE ‘kathryn’ instead of: Lecturer.name LIKE ‘kathryn’

Inner (Natural) Joins

Show students and their enrolled courses:SELECT Name, courseCode

FROM student, enrolIn

WHERE student.studentNo = enrolIn.studentNo;

A NATURAL or INNER join: only returns matching rows from the tables concerned If a student was not enrolled in a course (i.e. no entry in the enrolIn

table), then their name is not displayed

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.P_Id = Orders.P_Id

Two-Way Natural Joins

For example, to answer the question “Which Courses (by name) are taught by D. Hart?”

SELECT courseName

FROM course, lecturer

WHERE lecturer = name AND name = ‘D. Hart’;

This is another example of a two-way join (i.e. involves 2 tables), but joins can also be carried out between as many tables as necessary …

course(courseCode, courseName, lecturer)lecturer(name, department, telephone, title)

Three-Way Natural Joins

Example: to answer the question “Which Courses (by Course name) is Jane Doe enrolled in?

SELECT courseName

FROM course, student, enrolIn

WHERE course.courseCode = enrolIn.courseCode

AND student.studentNo = enrolIn.studentNo

AND name = ‘Jane Doe’;

student(studentNo, Name, DOB, Address, Gender, Degree)enrolIn(studentNo, courseCode, Mark)course(courseCode, courseName, Lecturer)

In-class Exercise 1

Exercise:

List the names (last & other) and teams of all players

Solution:

SELECT lastName, otherNames, teamName

FROM player, playsFor

WHERE player.playerID = playsFor.playerID;

player ( playerID, lastName, otherNames, ………)

playsFor ( playerID, teamName )

In-class Exercise 2

Exercise:Show the names and addresses for the coaches of the ‘Dead Heads’ team.

Solution:

SELECT coach.coachName, addressFROM coach, coachesWHERE coach.coachName = coaches.coachName

AND teamName = ‘Dead Heads’;

coach ( coachName, phoneNo, address, DOB )

coaches ( coachName, teamName, startDate, endDate )

Alternative Syntax for Joins

There is an alternative join syntax that specifies the join attributes in the FROM clause rather than the WHERE clause

This syntax applies to joins other than Natural Joins MsAccess use this alternative syntax

SELECT column1, column2

FROM table1 JOIN table2 ON join-condition;

Alternative Syntax Example

List the names and teams of all players:

SELECT lastName, otherNames, teamNameFROM player JOIN playsFor ON player.playerID = playsFor.playerID;

player ( playerID, lastName, otherNames, ………)

playsFor ( playerID, teamName )

Other Alternative Syntax for Joins

Alternative 2SELECT column1, column2

FROM table1 JOIN table2 USING column1;

Alternative 3SELECT column1, column2

FROM table1 NATURAL JOIN table2;

Alternative 4SELECT column1, column2

FROM table1 INNER JOIN table2;

Note: The alternatives you use may depend on •Your personal preference•The software you are using

Outer Joins

A NATURAL join: only returns matching rows from the tables concerned

An OUTER join: permit rows to be included in the result, even when there is no match in one of the

tables There are two kinds:

LEFT JOIN RIGHT JOIN

Left (Outer) Joins

The LEFT JOIN keyword returns all rows from the left (first mentioned) table, even if there are no matches in the right (second mentioned) table.

Non matching rows are completed with null values

Right and Full (Outer) Joins

The RIGHT JOIN keyword returns all the rows from the right (second mentioned) table even if there are no matches in the left (first mentioned) table.

Non matching rows are completed with null values

The FULL JOIN keyword returns all rows from both tables, even if there is no match in the other table.

Non matching rows are completed with null values

See: http://www.w3schools.com/sql/sql_join_right.asp and http://www.w3schools.com/sql/sql_join_full.asp for worked examples

Don’t forget the JOIN clause!

What happens if you use more than one table in your query but forget to “join” the tables?

Your result will be the Cartesian Product Ie: every row from Table 1 combined with every row from Table 2

Eg: If table 1 has 10 rows and Table 2 has 6 rows, your result will have 60 rows.

This is a BAD THING! Your query can produce a huge result table – check your joins!

Example Missing JOIN Clause

SELECT Employee.EmpNo, Employee.Name, EmpDept, Dept.Name, Dept.Location

FROM Dept, Employee;

Result table : 15 rows

The result from the above query (with no join specified) is 3x5=15 rows.

Notice the spurious, and incorrect data: Eg: employee Marcus Aarents is shown as working at all three

departments A join would ensure that only the valid rows are selected might

be:

WHERE employee.EmpDept = dept.Name

Example – no JOIN clause

Session 2, 2010 25

Only 5 rows are valid (ie where Dept names match)

Unions

The UNION operator is used to combine the result-set of two or more SELECT statements.

Hint: this may be of use for Ass 2

Each SELECT statement within the UNION must have: The same number of columns. With the same data types. In the same order.

Caveat: The UNION operator selects distinct values by default. Use UNION ALL for duplicates.

Summary

SQL is use to retrieve data from tables in a Relational database

When retrieving data from more than one table, those tables must be joined using matching PK and FK values.

After today’s lecture you should be able to use SQL: Joins Unions