PCRepairNorthShore: SQL Table Joins

Friends

A SQL JOIN combines data from two or more tables based on matching keys indicated in the SELECT statement. The matching keys are what creates the relationship between tables and uniquely identify the rows in a table.

INNER JOIN: This is the most common type of join and is considered the default join type. The inner join, sometimes referred to as an EQIJOIN, only brings back data that is a match between both tables being joined. In the example below:

SELECT statement states that we want all (*) columns from both tables.

FROM indicates the first table wanted.

INNER JOIN keyword specifies the other table we want to join to, the Orders table.

ON keyword is used in conjunction with the INNER JOIN keyword to indicate how the two tables are to be joined, in this example, the CustomerID of the Customers and Orders table. Customers.CustomerID and Orders.CustomerID is the format because the CustomerID name column is the same in both tables.

Example:

SELECT *

FROM Customers

INNER JOIN Orders

On Customers.CustomerID = Orders. CustomerID

note: If you fail to indicate the relationship between the two tables, you will end up with a Cartesian product. A Cartesian product causes each row from the first table to be multiplied by the total number or rows from the second table.

QueryCustOrders: List Customers Name and Date of Purchases.

Use an INNER JOIN to extract data from the Customers and Orders table.

**Customer**
CustomerID	FirstName	LastName	HomePhone	Address	State	City	PostalCode
1	Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
2	Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
3	Adam	Williams	(898) 321-2234	3890-A Cherry loop	ND	Fargo	58729
4	Jacob	Lin		2609 40th Ave S	OK	Tulsa	36711
5	Timothy	Coney	(202) 827-4331	1900 3rd St. N	OR	Salem	44812
6	Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
7	Tina	West	(813) 565-4984	1000 45th Ave N	FL	Miami	98911
8	Shawn	Leaven	(813) 248-8854	1908 22nd Ave S	FL	Miami	98912
9	Jackie	Justin		1800 4rd St. N	FL	Miami	98902
10	Lilly	Ward	(813) 244-5522	1010 33rd Ave N	FL	Miami	98909
12	Larry	Carr	(808) 423-7894	114 - C 45th Ave N	HI	Honolulu	96818
13	Lisa	Glenn	(808) 421-7515	1542 33rd Ave N	HI	Honolulu	96818

**Orders**
CustomerID	PlanID	OrderDate	CancelationDate
1	D1002	11/11/2009
10	D1002	3/15/2008	3/30/2001
10	D1003	3/15/2008
10	D2202	1/1/2010
2	W1001	3/15/2011
2	L2003	3/15/2011
2	I1001	3/20/2011
3	L2002	7/10/2010
3	L2001	7/10/2010
4	L2004	2/15/2011
7	L2004	5/14/2011
6	D1002	11/4/2010
6	D1101	11/4/2010
5	I1001	6/6/2009
8	I1001	3/14/2009
6	I1001	11/4/2010
7	L2001	5/13/2011
4	L2001	2/15/2011
8	L2001	3/17/2009
8	L2002	3/17/2009
9	L2003	6/11/2010

Select the FirstName, LastName, and Order Date from the Customers and Orders table. In the WHERE clause, specify the relationship between the two tables, CustomerID.

SELECT FirstName, LastName, OrderDate

FROM Customer, Orders

WHERE Customer.CustomerID = Orders.CustomerID;

Results:

**QueryCustOrders**
FirstName	LastName	OrderDate
Steve	Dentins	11/11/2009
Lilly	Ward	3/15/2008
Lilly	Ward	3/15/2008
Lilly	Ward	1/1/2010
Sam	Elliot	3/15/2011
Sam	Elliot	3/15/2011
Sam	Elliot	3/20/2011
Adam	Williams	7/10/2010
Adam	Williams	7/10/2010
Jacob	Lin	2/15/2011
Tina	West	5/14/2011
Jill	Stephens	11/4/2010
Jill	Stephens	11/4/2010
Timothy	Coney	6/6/2009
Shawn	Leaven	3/14/2009
Jill	Stephens	11/4/2010
Tina	West	5/13/2011
Jacob	Lincoln	2/15/2011
Shawn	Leaven	3/17/2009
Shawn	Leaven	3/17/2009
Jackie	Justin	6/11/2010

Self-Join: Lets you join a table to itself and are tables that have a column that refers to another column in the same table.

alias: Use the AS keyword to use a substitute name for a field.

natural join: a type of equi-join where the common columns are compared with each other and listed only once. Most experts agree this type of join is dangerous, in case a new column is mistakenly added with the same name.

QuerySvcPlanDesc: List PlanIds and ServicePlan Descriptions. Determine if there are duplicate PlanIds with different ServicePlan Descriptions. Every PlanId is unique, so using a Self-Join will display any duplicate PlanId with a different ServicePlan Description.

**ServicePlans**
PlanID	PlanName	PlanDescription	PlanPrice	DiscountedPrice
D1002	Digital Cable	Digital cable plan	$40.00	$35.00
D1003	Digital Phone	Cell phone plan	$40.00	$35.00
D1101	Data IP Services A	Plan A Data/IP services	$100.00	$85.00
D2202	Data IP Services B	Plan B Data/IP services	$150.00	$125.00
E1001	Email	Premium e-mail	$9.99	$6.99
I1001	Internet	Internet/email	$19.99	$16.99
L2001	Local Phone	In state calling	$39.00	$29.99
L2002	Long Distance A	Plan A Out of state per/min	$0.12	$0.11
L2003	Long Distance B	Plan B Out of state per/min	$0.09	$0.08
L2004	Long Distance C	Plan C Out of state per/min	$0.10	$0.09
W1001	Web Hosting	hosting, site creation	$19.99	$16.99

Create alias names because of the self-join so the DBMS thinks the tables are different. Check PlanIDs for equality and PlanDescriptions for inequality.

SELECT p1.PlanID, p1.PlanDescription,

p2.PlanID, p2.PlanDescription

FROM ServicePlans AS p1, ServicePlans AS p2

WHERE p1.PlanID = p2.PlanID

AND p1.PlanDescription <> p2.PlanDescription;

Results:

**QuerySvcPlanDesc**
p1.PlanID	p1.PlanDescription	p2.PlanID	p2.PlanDescription

QueryCustOrderSvcPlan: Create a query to get data from three different tables, Customer, Orders and ServicePlan, tables. Create aliases for the tables. Display the D1002 PlanID in the Orders table.

**Customer**
CustomerID	FirstName	LastName	HomePhone	Address	State	City	PostalCode
1	Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
2	Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
3	Adam	Williams	(898) 321-2234	3890-A Cherry loop	ND	Fargo	58729
4	Jacob	Lin		2609 40th Ave S	OK	Tulsa	36711
5	Timothy	Coney	(202) 827-4331	1900 3rd St. N	OR	Salem	44812
6	Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
7	Tina	West	(813) 565-4984	1000 45th Ave N	FL	Miami	98911
8	Shawn	Leaven	(813) 248-8854	1908 22nd Ave S	FL	Miami	98912
9	Jackie	Justin		1800 4rd St. N	FL	Miami	98902
10	Lilly	Ward	(813) 244-5522	1010 33rd Ave N	FL	Miami	98909
12	Larry	Carr	(808) 423-7894	114 - C 45th Ave N	HI	Honolulu	96818
13	Lisa	Glenn	(808) 421-7515	1542 33rd Ave N	HI	Honolulu	96818

**Orders**
CustomerID	PlanID	OrderDate	CancelationDate
1	D1002	11/11/2009
10	D1002	3/15/2008	3/30/2001
10	D1003	3/15/2008
10	D2202	1/1/2010
2	W1001	3/15/2011
2	L2003	3/15/2011
2	I1001	3/20/2011
3	L2002	7/10/2010
3	L2001	7/10/2010
4	L2004	2/15/2011
7	L2004	5/14/2011
6	D1002	11/4/2010
6	D1101	11/4/2010
5	I1001	6/6/2009
8	I1001	3/14/2009
6	I1001	11/4/2010
7	L2001	5/13/2011
4	L2001	2/15/2011
8	L2001	3/17/2009
8	L2002	3/17/2009
9	L2003	6/11/2010

**ServicePlans**
PlanID	PlanName	PlanDescription	PlanPrice	DiscountedPrice
D1002	Digital Cable	Digital cable plan	$40.00	$35.00
D1003	Digital Phone	Cell phone Plan	$40.00	$35.00
D1101	Data IP Services A	Plan A Data/IP services	$100.00	$85.00
D2202	Data IP Services B	Plan B Data/IP services	$150.00	$125.00
E1001	Email	Premium e-mail	$9.99	$6.99
I1001	Internet	Internet/email	$19.99	$16.99
L2001	Local Phone	In state calling	$39.00	$29.99
L2002	Long Distance A	Plan A Out of state per/min	$0.12	$0.11
L2003	Long Distance B	Plan B Out of state per/min	$0.09	$0.08
L2004	Long Distance C	Plan C Out of state per/min	$0.10	$0.09
W1001	Web Hosting	hosting, site creation	$19.99	$16.99

SELECT O.*, C.FirstName, C.LastName, SP.PlanName

FROM Customer AS C, Orders AS O, ServicePlans AS SP

WHERE C.CustomerID = O.CustomerID

AND SP.PlanID = O.PlanID

AND O.PlanID = 'D1002';

Results:

**QueryCustOrderSvcPlan**
CustomerID	PlanID	OrderDate	CancelationDate	FirstName	LastName	PlanName
1	D1002	11/11/2009		John	Dentins	Digital Cable
10	D1002	3/15/2008	3/30/2001	Lilly	Ward	Digital Cable
6	D1002	11/4/2010		Jill	Stephens	Digital Cable

OUTER JOIN: Does not require a match between the records in the joined tables. Each record is retained, even if no matching record exists. There are three types of outer joins:

LEFT OUTER JOIN (LEFT JOIN). Selects all records to the left of the LEFT OUTER JOIN keyword in the FROM clause.
RIGHT OUTER JOIN (RIGHT JOIN). Selects all records to the right of the RIGHT OUTER JOIN keyword in the FROM clause.
FULL OUTER JOIN (FULL JOIN). Selects all records from both tables in both directions, even if there is no match.

note: MYSQL does not recognize a full join.

QueryCustNoOrder: Create a query to list all customers ids with PlanIds, even if they do not have an order. Use an Outer Join.

**Customer**
CustomerID	FirstName	LastName	HomePhone	Address	State	City	PostalCode
1	Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
2	Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
3	Adam	Williams	(898) 321-2234	3890-A Cherry loop	ND	Fargo	58729
4	Jacob	Lin		2609 40th Ave S	OK	Tulsa	36711
5	Timothy	Coney	(202) 827-4331	1900 3rd St. N	OR	Salem	44812
6	Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
7	Tina	West	(813) 565-4984	1000 45th Ave N	FL	Miami	98911
8	Shawn	Leaven	(813) 248-8854	1908 22nd Ave S	FL	Miami	98912
9	Jackie	Justin		1800 4rd St. N	FL	Miami	98902
10	Lilly	Ward	(813) 244-5522	1010 33rd Ave N	FL	Miami	98909
12	Larry	Carr	(808) 423-7894	114 - C 45th Ave N	HI	Honolulu	96818
13	Lisa	Glenn	(808) 421-7515	1542 33rd Ave N	HI	Honolulu	96818

**Orders**
CustomerID	PlanID	OrderDate	CancelationDate
1	D1002	11/11/2009
10	D1002	3/15/2008	3/30/2001
10	D1003	3/15/2008
10	D2202	1/1/2010
2	W1001	3/15/2011
2	L2003	3/15/2011
2	I1001	3/20/2011
3	L2002	7/10/2010
3	L2001	7/10/2010
4	L2004	2/15/2011
7	L2004	5/14/2011
6	D1002	11/4/2010
6	D1101	11/4/2010
5	I1001	6/6/2009
8	I1001	3/14/2009
6	I1001	11/4/2010
7	L2001	5/13/2011
4	L2001	2/15/2011
8	L2001	3/17/2009
8	L2002	3/17/2009
9	L2003	6/11/2010

SELECT Customer.CustomerID, Orders.PlanID

FROM Customer LEFT OUTER JOIN Orders

ON Customer.CustomerID = Orders.CustomerID;

note: LEFT OUTER JOIN selects all the records from

the table noted to the left of the keyword LEFT OUTER JOIN.

Results:

**QueryCustNoOrders**
CustomerID	PlanID
1	D1002
2	W1001
2	L2003
2	I1001
3	L2002
3	L2001
4	L2004
4	L2001
5	I1001
6	D1002
6	D1101
6	I1001
7	L2001
7	L2004
8	I1001
8	L2001
8	L2002
9	L2003
10	D2202
10	D1003
10	D1002
12
13

Every customer id and planid is displayed, including the customer ids with no orders.

note: Some DBMS's do not recognize the LEFT OUTER JOIN keyword. The following operator is used, instead of the LEFT OUTER JOIN keyword and is used in the WHERE clause: *=

Example:

SELECT Customer.CustomerID, Orders.PlanID

FROM Customer, Orders

WHERE Customer.CustomerID *= Orders.CustomerID;

Versions of Oracle 9i and greater can use the LEFT OUTER JOIN, and the older (+) syntax.

QueryCustSvcPlanNoOrder: List Customer PlanIds and ServicePlans, even the ServicePlans that have not been ordered. Use the RIGHT OUTER JOIN.

SELECT Orders.CustomerID, ServicePlans.PlanID

FROM Orders RIGHT OUTER JOIN ServicePlans

ON Orders.PlanID = ServicePlans.PlanID;

note: RIGHT OUTER JOIN selects all the records from

the table noted to the right of the keyword RIGHT OUTER JOIN.

Results:

QueryCustSvcPlanNoOrder

CustomerID PlanID

10 D1002

1 D1002

6 D1002

10 D1003

6 D1101

10 D2202

6 I1001

8 I1001

5 I1001

2 I1001

7 L2001

4 L2001

8 L2001

3 L2001

3 L2002

8 L2002

2 L2003

9 L2003

7 L2004

4 L2004

2 W1001

E1001

**QueryCustSvcPlanNoOrder**
CustomerID	PlanID
10	D1002
1	D1002
6	D1002
10	D1003
6	D1101
10	D2202
6	I1001
8	I1001
5	I1001
2	I1001
7	L2001
4	L2001
8	L2001
3	L2001
3	L2002
8	L2002
2	L2003
9	L2003
7	L2004
4	L2004
2	W1001
	E1001

note: Some DBMS's do not recognize the RIGHT OUTER JOIN keyword. The following operator is used, instead of the RIGHT OUTER JOIN keyword and is used in the WHERE clause: =*

Example:

SELECT Orders.CustomerID, ServicePlans.PlanID

FROM Orders, ServicePlans

WHERE Orders.PlanID =* ServicePlans.PlanID;

Versions of Oracle 9i and greater can use the RIGHT OUTER JOIN, and the older (+) syntax.

http://www.w3schools.com/sql/sql_join.asp

http://en.wikipedia.org/wiki/Join_(SQL)

http://www.tizag.com/sqlTutorial/sqljoin.php

http://www.amazon.com/Language-SQL-Access-Relational-Databases/dp/143545751X