Transcript Slide 1

JOINS
cis 407
Subqueries
Correlated Subqueries
Exists operator
performance considerations
Examples
Nested subquery
• Loose query syntax
• Select <select list>
from <sometable>
where <someColumn> =
(select <SINGLE COLUMN>
from <someTable>
where <condition that results in only
one row returned>)
Nested Subquery
• Select <select list>
from <sometable>
where <someColumn> IN
(select <SINGLE COLUMN>
from <someTable>
where <condition>)
• Find the product IDs for items sold on the first day
• Select DISTINCT o.orderDate, od.ProductID
from orders o join [order details] od on o.orderID =
od.orderid
where orderDate = ‘7/4/1996’ – first order date in
system
– But bit of a kludge so…
Nested Subquery
• Select DISTINCT o.orderDate,
od.ProductID
from orders o join [order details] od on
o.orderID = od.orderid
where orderDate =
(select min(orderdate) from orders)
Nested subquery w/ multiple values
• Use pubs – find all the stores that have
discount records
• Select stor_id as ‘store id”, stor_name as
“store name”
from stores
where stor_id in
(select stor_id from discounts)
• Same as: select s.stor_id, sotr_name from
stores s join discounts d on s.stor_id =
d.stor_id
Not in and outerjoin
• Find all the stores that don’t have
matching discount records
• Select s.stor_name
from discounts d right outerjoin stores s
on d.stor_id = s.stor_id
where d.stor_id is null
• Select stor_id, stor_name
from stores where stor_id not in
(select stor_id from discounts
where stor_id in not null)
Correlated subqueries
• PAY ATTENTION – Can be tricky
• Orderid & orderDate for first order in the
system FOR EACH CUSTOMER
• Select o1.customerid, o.ordeid,
o1.orderdate
from orders o1
where o1.orderdate =
(select min(o2.orderdate)
from orders o2
where o2.customerid = o1.customerid)
Correlated subqueries
• Same query but want to add customer
name
• Select cu.companyName,
(select min(orderdate)
from orders o
where o.customerid = cu.customerid)
as “order date”
from customers cu
ISNULL function
• Isnull function accepts a variable or
expression and tests it for a null value
• Orderid/orderDate for first order in the
system FOR EACH CUSTOMER NAME
• Select cu.companyName,
ISNULL(CAST ((select min(o.orderdate)
from orders o
where o.customerid = u.customerid)
as varchar), ‘ never ordered’)
from customers cu
Exists operator
• Customers that placed at least one order
• Select customerid, companyName
from customers cu
where exists
(select orderid
from orders o
where o.customerid = cu.customerid)
• Select distinct cu.customerid, cu.companyname
from customers cu join orders o on
cu.customerid = o.customerid
• But top one does not require full row by row join
Customers who have not ordered
anything
• Use northwind
• Select c.customerid, companyname
from customers c left outerjoin orders o
on c.customerid = o.customerid
where o.customerid is null
• Select customerid, companyname
from customers cu
where not exists (select orderid
from orders o
where o.customerid = cu.customerid)
Other exist uses
• If exists (select * from sysobjects where id
= object_id(N’[dbo.[shippers]’) and
objectProperty(id,n’isusertable’) = 1) drop
table [dbo].[shppers]
Query performance
•
•
•
•
Page 204-205
How things USUALLY work
When in doubt – or even when your not!
TEST!!!!!!!!
Math functions
• Pg 603
• Note ceiling and floor
Metadata functions
• Functions about the database and DB
objects
• Col_length, col_name, …
• Pg 608
Security Functions
• Pg 620
• Has_DBaccess, is_member,
is_sbvrolemember, suser_id, suser_name,
suser_SID, user, user_id,
String Functions
• Pg 623
• Ascii, char, charindex, differnce, left, len,
lower, ltrim. Nchar, parindex, quotename,
replace, replicate, reverse, right, rtrim,
soundex, space, str, stuff, substring,
nicode, upper
System functions
• Pg 628
• App_name, case, cast and convert, coalesce,
collationproperty, current_timestamp,
current_user, datalength, formatmessage,
getansinull, host_id, host_name, ident_current,
ident_incr, ident_seed, identity, isdate, isnull,
isnumeric, newid, nullif, parsename,
permissions, rowcount_big, scope_property,
session_user, sessionproperty, stats_date,
system_user, user_name
Aggregate functions
• Pg 597 appendix B
• Avg, checksum, checksum=agg, count,
count_big, grouping, max, min, stdev,
stdevp, sum, var, varp
Date time functions
• Dateadd, Datediff, Datename, Datepart
Day, Getdate, getUTCdate, month
year
• See details pg 601 appendix B