No Slide Title

Chapter 04
How to retrieve data
from two or more
tables
MIT 22033, Database Management System
By: S. Sabraz Nawaz
MIT 22033 By. S. Sabraz Nawaz
Slide 2
Inner Join
MIT 22033 By. S. Sabraz Nawaz
Slide 3
The explicit syntax for an inner join
SELECT select_list
FROM table_1
[INNER] JOIN table_2
ON join_condition_1
[[INNER] JOIN table_3
ON join_condition_2]...
Notes
 The INNER keyword is optional and is seldom used.
 This syntax for coding an inner join can be referred to as the
explicit syntax. It is also called the SQL-92 syntax because it
was introduced by the SQL-92 standards.
MIT 22033 By. S. Sabraz Nawaz
Slide 4
How to code an inner join
 A join is used to combine columns from two or more tables into a
result set based on the join conditions you specify.
 For an inner join, only those rows that satisfy the join condition are
included in the result set.
 A join condition names a column in each of the two tables involved
in the join and indicates how the two columns should be compared.
 In most cases, you’ll join two tables based on the relationship
between the primary key in one table and a foreign key in the other
table.
 You can also join tables based on relationships not defined in the
database. These are called ad hoc relationships.
 If the columns in a join condition have the same name, you have to
qualify them with the table names. To code a qualified column
name, type the table name, a period, then the column name.
MIT 22033 By. S. Sabraz Nawaz
Slide 5
A SELECT statement that joins the Vendors and
Invoices tables
SELECT InvoiceNumber, VendorName
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
The result set
(114 rows)
MIT 22033 By. S. Sabraz Nawaz
Slide 6
When and how to use correlation names
 Correlation names are temporary table names assigned in the
FROM clause. You can use them when long table names make
qualified column names long or confusing.
 A correlation name can also be called a table alias.
 If you assign a correlation name to a table, you must use that name
to refer to the table within your query. You can’t use the original
table name.
 Although the AS keyword is optional, it’s a good idea to use it
because it makes the FROM clause easier to read.
 You can use a correlation name for any table in a join without
using correlation names for all of the other tables.
 Use correlation names whenever they simplify or clarify the query.
Avoid using correlation names when they make a query more
confusing or difficult to read.
MIT 22033 By. S. Sabraz Nawaz
Slide 7
The syntax for an inner join that uses
correlation names
SELECT select_list
FROM table_1 [AS] n1
[INNER] JOIN table_2 [AS] n2
ON n1.column_name operator n2.column_name
[[INNER] JOIN table_3 [AS] n3
ON n2.column_name operator n3.column_name]...
MIT 22033 By. S. Sabraz Nawaz
Slide 8
An inner join with correlation names that make
the query more difficult to read
SELECT InvoiceNumber, VendorName, InvoiceDueDate,
InvoiceTotal - PaymentTotal - CreditTotal
AS BalanceDue
FROM Vendors AS v JOIN Invoices AS i
ON v.VendorID = i.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY InvoiceDueDate DESC
(40 rows)
MIT 22033 By. S. Sabraz Nawaz
Slide 9
An inner join with a correlation name that
simplifies the query
SELECT InvoiceNumber, InvoiceLineItemAmount,
InvoiceLineItemDescription
FROM Invoices JOIN InvoiceLineItems AS LineItems
ON Invoices.InvoiceID = LineItems.InvoiceID
WHERE AccountNo = 540
ORDER BY InvoiceDate
(8 rows)
MIT 22033 By. S. Sabraz Nawaz
Slide
10
Inner joins that join more than two tables
 You can think of a multi-table join as a series of two-table joins
proceeding from left to right.
 The first two tables are joined to produce an interim result set or
interim table. Then, the interim table is joined with the next table,
and so on.
MIT 22033 By. S. Sabraz Nawaz
Slide
11
A SELECT statement that joins the Vendors and
Invoices tables
SELECT InvoiceNumber, VendorName
FROM Vendors, Invoices
WHERE Vendors.VendorID = Invoices.VendorID
The result set
MIT 22033 By. S. Sabraz Nawaz
Slide
12
A statement that joins four tables
USE AP
SELECT VendorName, InvoiceNumber, InvoiceDate,
InvoiceLineItemAmount AS LineItemAmount, AccountDescription
FROM
Vendors
JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
JOIN GLAccounts ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY VendorName, LineItemAmount DESC
The result set
MIT 22033 By. S. Sabraz Nawaz
Slide
13
Outer Join
MIT 22033 By. S. Sabraz Nawaz
Slide
14
The explicit syntax for an outer join
SELECT select_list
FROM table_1
{LEFT|RIGHT|FULL} [OUTER] JOIN table_2
ON join_condition_1
[{LEFT|RIGHT|FULL} [OUTER] JOIN table_3
ON join_condition_2]...
How to code an outer join
 An outer join retrieves all rows that satisfy the join condition, plus
unmatched rows in one or both tables.
 In most cases, you use the equal operator to retrieve rows with
matching columns. However, you can also use any of the other
comparison operators.
 When a row with unmatched columns is retrieved, any columns
from the other table that are included in the result set are given null
values.
MIT 22033 By. S. Sabraz Nawaz
Slide
15
What outer joins do
Joins of this type
Left outer join
Right outer join
Full outer join
MIT 22033 By. S. Sabraz Nawaz
Keep unmatched rows from
The first (left) table
The second (right) table
Both tables
Slide
16
A SELECT statement that uses a left outer join
SELECT VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors LEFT JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
ORDER BY VendorName
(202 rows)
MIT 22033 By. S. Sabraz Nawaz
Slide
17
The Departments table
MIT 22033 By. S. Sabraz Nawaz
The Employees table
Slide
18
A left outer join
SELECT DeptName, Departments.DeptNo, LastName
FROM Departments LEFT JOIN Employees
ON Departments.DeptNo = Employees.DeptNo
MIT 22033 By. S. Sabraz Nawaz
Slide
19
A right outer join
SELECT DeptName, Employees.DeptNo, LastName
FROM Departments RIGHT JOIN Employees
ON Departments.DeptNo = Employees.DeptNo
MIT 22033 By. S. Sabraz Nawaz
Slide
20
A full outer join
SELECT DeptName, Departments.DeptNo,
Employees.DeptNo, LastName
FROM Departments FULL JOIN Employees
ON Departments.DeptNo = Employees.DeptNo
MIT 22033 By. S. Sabraz Nawaz
Slide
21
The Departments table
The Employees table
MIT 22033 By. S. Sabraz Nawaz
The Projects table
Slide
22
A SELECT statement that joins the three tables
using left outer joins
SELECT DeptName, LastName, ProjectNo
FROM Departments
LEFT JOIN Employees
ON Departments.DeptNo = Employees.DeptNo
LEFT JOIN Projects
ON Employees.EmployeeID = Projects.EmployeeID
ORDER BY DeptName, LastName, ProjectNo
The result set
MIT 22033 By. S. Sabraz Nawaz
Slide
23
A SELECT statement that joins the three tables
using full outer joins
SELECT DeptName, LastName, ProjectNo
FROM Departments
FULL JOIN Employees
ON Departments.DeptNo = Employees.DeptNo
FULL JOIN Projects
ON Employees.EmployeeID = Projects.EmployeeID
ORDER BY DeptName
The result set
MIT 22033 By. S. Sabraz Nawaz
Slide
24
Assignment
• What is a Cross Join? Using the tables in the
Accounts Payable database, clearly explain with
SQL Script and its corresponding output.
MIT 22033 By. S. Sabraz Nawaz
Slide
25
Presentation
• What is Normalization?
MIT 22033 By. S. Sabraz Nawaz
Slide
26
?

Download Report