Sunday, March 10, 2013

SQL Table Joins


Friends
Print Friendly and PDF

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:
  1. LEFT OUTER JOIN (LEFT JOIN). Selects all records to the left of the LEFT OUTER JOIN keyword in the FROM clause.
  2. RIGHT OUTER JOIN (RIGHT JOIN). Selects all records to the right of the RIGHT OUTER JOIN keyword in the FROM clause.
  3. 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

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.






No comments:

Post a Comment

"Comment As:" anonymous if you would rather not sign into an account!