CSL-220: Database Management System

Semester: BSIT-4A
Fall 2021

Lab 05: Joining Tables

Objective(s) :
To learn the use of Joins in SELECT statement.

Joins

By using joins, you can retrieve data from two or more tables based on logical relationships between
the tables. Joins indicate how SQL should use data from one table to select the rows in another table.

A join condition defines the way two tables are related in a query by:

1. Specifying the column from each table to be used for the join. A typical join condition
specifies a foreign key from one table and its associated key in the other table.
 Specifying a logical operator (for example, = or <>,) to be used in comparing values from the
columns.

There are various forms of the JOIN clause. These will include:
 INNER JOIN
 OUTER JOIN (both LEFT and RIGHT)
 FULL OUTER JOIN
 CROSS JOIN

A JOIN does just what it sounds like—it puts the information from two tables together into one
result set. We can think of a result set as being a “virtual” table. It has both columns and rows, and
the columns have data types. How exactly does a JOIN put the information from two tables into a
single result set? Well, that depends on how you tell it to put the data together—that’s why there are
four different kinds of JOINs. The thing that all JOINs have in common is that they match one record
up with one or more other records to make a record that is a superset created by the combined
columns of both records.

For example, let’s take a record from a table we’ll call Films:

Now let’s follow that up with a record from a table called Actors:

With a JOIN, we could create one record from two records found in totally separate tables:
Departemnent of Computer Sciences 25/67 Fall 2021
CSL-220: Database Management System Lab 05: Joining Tables
 1) INNER JOINs
INNER JOINs are far and away the most common kind of JOIN. They match records
together based on one or more common fields, as do most JOINs, but an INNER JOIN
returns only the records where there are matches for whatever field(s) you have said are to be
used for the JOIN. In our previous examples, every record has been included in the result set
at least once, but this situation is rarely the case in the real world.
Let’s modify our tables and see what we would get with an INNER JOIN. Here’s our Films
table:

And our Actors table:

Using an INNER JOIN, our result set would look like this:

Syntax
SELECT <select list>FROM <first_table><join_type><second_table>[ON <join_condition>]

<join type > can be INNER JOIN, LEFT OUTER, RIGHT OUTER, FULL JOIN and CROSS JOIN

2) OUTER JOIN
The LEFT OUTER returns all rows from the left table (table_name1), even if there are no
matches in the right table (table_name2). While The RIGHT OUTER returns all the rows
from the right table (table_name2), even if there are no matches in the left table
(table_name1).

3) FULL OUTER JOIN

The FULL OUTER JOIN keyword return rows when there is a match in one of the tables
Departemnent of Computer Sciences 26/67 Fall 2021
CSL-220: Database Management System Lab 05: Joining Tables
 4) CROSS JOIN
This returns the Cartesian product of rows from tables in the join. In other words, it will
produce rows which combine each row from the first table with each row from the second
table

Example
Fire up the Management Studio and take a test drive of INNER JOINs using the following code
against Northwind:

SELECT *
FROM Products
INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
The results of this query, you should get something in the order of 77 rows back. There are several
things worth noting about the results:
a) The SupplierID column appears twice, but there’s nothing to say which one is from which
table.
b) All columns were returned from both tables.
c) The first columns listed were from the first table listed.

Departemnent of Computer Sciences 27/67 Fall 2021

CSL-220: Database Management System Lab 05: Joining Tables
 Exercises

Using StudentInformation database solve the following problems. You are required to pull
information from multiple tables

1. Select CourseID, CourseName and ProgramName using course and program table
2. Select CourseID, CourseName, ProgramName and semester name using course, program
and semester tables
3. Select StdID, StdName who have been studying other than BSCS program using Student and
Program Table
4. Select all visiting faculty names and course names taught by them using Teacher, Course and
StdCourseTeacher Tables
5. Select all courses that have been taught by Dr. Iman in Fall2020
6. Select all courses (CourseID, CourseName) along with their teacher names. All Course
records must be displayed.
7. Select all student names that have been taught by Dr Ahmed along with the course name
8. Select StdID, StdName, CourseID, CourseName, TeacherID, TeacherName, SemID and
SemesterName using Std, Course, Teacher, Semester and StdCourseTeacher tables
9. Select Distinct TeacherID, TeacherName of all teachers who have taught any course in
Fall2020 using Teacher and StdCourseTeacher tables
10. Select All TeacherID and TeacherName who have taught any course in any program using
Teacher and StdCourseTeacher Tables
11. Select all TeacherID, TeacherName who have or have not taught any course in any program
using Teacher and StdCourseTeacher Tables. (Note: All teacher records must be returned)
12. Select All TeacherID and TeacherName who have NOT taught any course in any department
or program. (Hint: Check Set Operators from w3schools.
https://www.w3schools.com/sql/sql_operators.asp)
13. Select Semesters in which no student has been enrolled in any course
14. Write a query that shows CourseName, TeacherName, SemesterName, Max, Min and
Average marks secured by students (Bonus Marks Query.)

Departemnent of Computer Sciences 28/67 Fall 2021

CSL-220: Database Management System Lab 05: Joining Tables