The Islamic University of Gaza
Faculty of Engineering
Dept. of Computer Engineering
Database Lab (ECOM 4113)
Lab 5
SQL
Retrieving Data from
Multiple Tables
Eng. Ibraheem Lubbad
An SQL JOIN clause is used to combine rows from two or more tables, views, or
materialized views, based on a common field between them.
The following list type of the SQL JOINs you can use:
1) Inner join
Return all rows that match the condition in both the tables
2) Outer join
LEFT (OUTER) JOIN:
Return all the rows from the left table in conjunction with the
matching rows from the right table. If there are no columns matching in the
right table, it returns NULL values.
RIGHT (OUTER) JOIN:
Return all the rows from the right table in conjunction with the
matching rows from the left table. If there are no columns matching in the
left table, it returns NULL values.
FULL (OUTER) JOIN:
Combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns row
from either table when the conditions are met and returns NULL value
when there is no match.
3) Natural join
It is based on the two conditions:
The JOIN is made on all the columns with the same name for quality
Removes duplicate columns from the result.
4) Cross join
It is the Cartesian product of the two tables involved.
5) Self
It is a JOIN (INNER, OUTER, etc) of a table to itself.
Join Conditions:
Natural:
Creates an implicit join base on columns that have the same name in both
tables, also common column must have the same datatype, otherwise
return error.
Using ( column_name) :
Creates an explicit join base on columns that have the same name in both
tables, usually use key attribute to join two table.
On:
creates an explicit join using join condition to bind to two table, use it when
key attribute in each column has different name, also we can add
conditional expression as where.
Note:
All INNER and OUTER keywords are optional
Join Conditions (natural, using, on) can be combined with any join type
In general, the syntax of an SQL JOIN clause is:
Syntax of SQL Select Statement
SELECT TABLE1. COLUMN, TABLE2. COLUMN
FROM TABLE1
[NATURAL JOIN TABLE2] |
[JOIN TABLE2 USING (COLUMN_NAME)] |
[JOIN TABLE2 ON (TABLE1. COLUMN_NAME = TABLE2. COLUMN_NAME)]
| [LEFT| RIGHT| FULL OUTER JOIN TABLE2 ON (TABLE1.
COLUMN_NAME = TABLE2. COLUMN_NAME)]| [CROSS JOIN TABLE2];
INNER JOIN
(INNER) JOIN: Return records that have matching values in both tables.
Syntax of SQL Select Statement
TABLE1 [INNER] JOIN TABLE2
USING (COLUMN_NAME) |
ON (TABLE1. COLUMN_NAME = TABLE2. COLUMN_NAME) ;
Example: Find a list of all students, displaying their ID, and name, dept_name, and
tot_cred, along with the courses that they have taken
Use NATURAL clause
SELECT ID ,NAME,DEPT_NAME,TOT_CRED,COURSE_ID
FROM STUDENT NATURAL JOIN TAKES ;
Use USING clause
SELECT ID , NAME,DEPT_NAME,TOT_CRED,COURSE_ID
FROM STUDENT JOIN TAKES
USING (ID)
Use on clause
SELECT STUDENT.ID ,NAME,DEPT_NAME,TOT_CRED,COURSE_ID
FROM STUDENT JOIN TAKES
ON STUDENT.ID=TAKES.ID ;
OUTER JOIN
By default, joining tables with the NATURAL JOIN, USING, or ON clauses results in
an inner join. Any unmatched rows are not displayed in the output. To return the
unmatched rows, you can use an outer join. An outer join returns all rows that
satisfy the join condition and also returns some or all of those rows from one
table for which no rows from the other table satisfy the join condition.
If the SELECT statement in which the JOIN operations appears has an asterisk (*)
in the select list, the asterisk will be expanded to the following list of columns (in
this order):
All the common columns.
Every column in the first (left) table that is not a common column.
Every column in the second (right) table that is not a common column
There are three types of outer joins:
1. LEFT OUTER
2. RIGHT OUTER
3. FULL OUTER
OUTER JOIN Syntax
TABLE1 { LEFT| RIGHT| FULL} [OUTER] JOIN TABLE2
USING (COLUMN_NAME) |
ON (TABLE1. COLUMN_NAME = TABLE2. COLUMN_NAME) ;
1) Left OUTER JOIN:
Example: Find INSTRUCTORs that do not work in any department
SELECT *
FROM INSTRUCTOR
LEFT JOIN DEPARTMENT
USING(DEPT_NAME);
Note first column condition attribute
Instructor table columns
Department table columns
Instructor “ibraheem “do not work in any department so all department attribute
returned as null value.
Also we can use natural condition to join between two table base common
column tables.
SELECT *
FROM INSTRUCTOR
NATURAL LEFT JOIN DEPARTMENT
2) Right OUTER JOIN:
SELECT *
FROM INSTRUCTOR
RIGHT JOIN DEPARTMENT
USING(DEPT_NAME);
There isn’t any instructor in the Department of Mathematics.
3) Full OUTER JOIN:
Combines LEFT OUTER JOIN and RIGHT OUTER JOIN
SELECT *
FROM INSTRUCTOR
FULL JOIN DEPARTMENT
USING(DEPT_NAME);
Left table
Right table
Self-join: Joining a Table to Itself
Example: Find the names of all instructors whose salary is greater than at least
one instructor in the Biology department.
SELECT DISTINCT T.NAME,T.SALARY,S.NAME, S.SALARY
FROM INSTRUCTOR T
JOIN INSTRUCTOR S
ON T.ID <> S.ID
WHERE T.SALARY > S.SALARY AND S.DEPT_NAME = 'Biology';
Join multiple tables:
To execute a join of three or more tables, first joins two of the tables based on
the join conditions comparing their columns and then joins the result to another
table based on join conditions containing columns of the joined tables and the
new table.
Example: Find all instructors names and all courses name they taught
SELECT NAME AS INSTRUCTOR_NAME, C.TITLE AS COURSE_NAME
FROM INSTRUCTOR I
JOIN TEACHES T
ON I.ID=T.ID
JOIN COURSE C
ON T.COURSE_ID=C.COURSE_ID;
Join with Grouping:
Apply aggregation function on table that containing columns of the joined tables
Example: For each course section offered in 2009, find the average total credits
( tot cred) of all students enrolled in the section, which had at least 2 students
SELECT COURSE_ID ,SEC_ID,SEMESTER,YEAR,AVG(TOT_CRED)
FROM TAKES NATURAL JOIN STUDENT
WHERE YEAR=2009
GROUP BY COURSE_ID ,SEC_ID,SEMESTER,YEAR
HAVING COUNT (ID) >= 2;
Example: Find the number of instructors in each department who teach a course
in the Spring 2010 semester
SELECT DEPT_NAME, COUNT (DISTINCT ID) AS INSTR_COUNT
FROM INSTRUCTOR NATURAL JOIN TEACHES
WHERE SEMESTER = 'Spring' AND YEAR = 2010
GROUP BY DEPT_NAME;
END
