code1:
USE pubs
SELECT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
      AND type = 'business')

output:
pub_name
--------------------
New Moon Books
Algodata Infosystems



下列兩種方式均可找出和出版商住在相同城市的作者：

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =ANY
   (SELECT city
   FROM publishers)
-- Or
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)

下列為任一種查詢的結果集：

au_lname         au_fname
--------         --------
Carson         Cheryl
Bennet         Abraham

(2 row(s) affected)


這兩種查詢均可找出住在以字母 B 開頭之城市的任何出版商所出版的書籍標題：

USE pubs
SELECT title
FROM titles
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE city LIKE 'B%')
-- Or
USE pubs
SELECT title
FROM titles
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE pub_id = titles.pub_id
      AND city LIKE 'B%')

下列為任一種查詢的結果集：

title
----------------------------------------------------
The Busy Executive's Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
But Is It User Friendly?
Secrets of Silicon Valley
Net Etiquette
Is Anger the Enemy?
Life Without Fear
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm

(11 row(s) affected)


code2:
SELECT top 100 tc.sa_ss, pc_date, cancel_month
FROM tbl_customer_cancellation as tc
WHERE EXISTS
(SELECT * FROM tbl_sdk_ml_order as ta
inner join tbl_sdk_ml_line as tb
on ta.ord_no = tb.ord_no
where ta.sa_ss=tc.sa_ss
and substring(item, 1, 4)='0701'
and price<800
and ta.ord_date>=tc.pc_date
and ta.ord_date<=tc.cancel_month
)

code3:
update tbl_customer_cancellation as tc
set Enroll_Type='Special'
WHERE EXISTS
(SELECT * FROM tbl_sdk_ml_order as ta
inner join tbl_sdk_ml_line as tb
on ta.ord_no = tb.ord_no
where ta.sa_ss=tc.sa_ss
and substring(item, 1, 4)='0701'
and price<800
and ta.ord_date>=tc.pc_date
and ta.ord_date<=tc.cancel_month
)