Lab 06 Joins 2024

CS220: Database Systems Page 1
Department of Computer Science
CS220: Database Systems
Class: BSCS
Lab 06: SQL Joins
Date: 14 March, 2024
Time: 10:00-01:00 & 02:00-05:00
Instructor: Dr. Shams Uddin Qazi
Lab Engineer: Syed Muhammad Ali Musa
CLO4: Demonstrate SQL queries to retrieve information from a relational
database

CS220: Database Systems Page 2
Lab 07: SQL Joins
Introduction
A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a
table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.
Objectives
After performing this lab students should be able to: 1. Design SQL queries to retrieve data from multiple tables by
using JOIN operation. 2. Select among INNER, OUTER, NATURAL, CROSS join as and when required.
Tools/Software Requirement
• MySQL Community Server 5.6
• MySQL Workbench 6.1
• Sakila Database
Description
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.

CS220: Database Systems Page 3
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1

CS220: Database Systems Page 4
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Self Join
SQL Self Join
A self join is a regular join, but the table is joined with itself.

CS220: Database Systems Page 5
Self Join Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.
1. This lab assumes that MySQL Community Server is running, Sakila database has been
loaded using MySQL Workbench, and the query window is open.
2. Execute the JOIN queries on Sakila including cross join, inner join, natural join, outer
join (left outer join, right outer join, and full outer join) and observe how each join
operation differs from the other.
3. For your practice, create the following two tables and execute the following queries to better
understand how each join operation works.
Users
Course
id
name
course_id
1
Alice
1
2
Bob
1
3
Caroline
2
4
David
5
5
Emma
(NULL)
course_id
name