SQL Server/T-SQL Tutorial/Query/Exists
Содержание
- 1 A common use for EXISTS is to answer a query such as Show me the titles for which no stores have sales.
- 2 A simple subquery using the EXISTS clause.
- 3 EXISTS() returns a row in the outer query when any records are returned by a subquery.
- 4 IF EXISTS
- 5 NOT EXISTS with correlated subquery
- 6 The syntax of a subquery that uses the EXISTS operator
A common use for EXISTS is to answer a query such as Show me the titles for which no stores have sales.
7> CREATE TABLE sales(
8> stor_id char(4) NOT NULL,
9> ord_num varchar(20) NOT NULL,
10> ord_date datetime NOT NULL,
11> qty smallint NOT NULL,
12> payterms varchar(12) NOT NULL,
13> title_id varchar(80)
14> )
15> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE titles(
4> title_id varchar(20),
5> title varchar(80) NOT NULL,
6> type char(12) NOT NULL,
7> pub_id char(4) NULL,
8> price money NULL,
9> advance money NULL,
10> royalty int NULL,
11> ytd_sales int NULL,
12> notes varchar(200) NULL,
13> pubdate datetime NOT NULL
14> )
15> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT T.title_id, title FROM titles T
3> WHERE NOT EXISTS
4> (SELECT 1
5> FROM sales S
6> WHERE T.title_id=S.title_id )
7> GO
title_id title
-------------------- --------------------------------------------------------------------------------
4 Prolonged
5 With
6 Valley
7 Any?
8 Fifty
(5 rows affected)
1>
2> drop table titles;
3> drop table sales;
4> GO
1>
A simple subquery using the EXISTS clause.
5>
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE sales(
3> stor_id char(4) NOT NULL,
4> ord_num varchar(20) NOT NULL,
5> ord_date datetime NOT NULL,
6> qty smallint NOT NULL,
7> payterms varchar(12) NOT NULL,
8> title_id varchar(80)
9> )
10> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT title, price
3> FROM titles
4> WHERE EXISTS
5> (SELECT *
6> FROM sales
7> WHERE sales.title_id = titles.title_id
8> AND qty > 30)
9> GO
title price
-------------------------------------------------------------------------------- ---------------------
Secrets 20.0000
(1 rows affected)
1>
2>
3> drop table sales;
4> drop table titles;
5> GO
EXISTS() returns a row in the outer query when any records are returned by a subquery.
Subquery can be any SELECT statement, directly related or not, to the main SELECT statement.
EXISTS() is often used in correlated subqueries to either mimic the behavior of a join.
It doesn"t really matter what column or columns are returned in the subquery because you don"t actually use these values.
9> CREATE TABLE employee(
10> id INTEGER NOT NULL PRIMARY KEY,
11> first_name VARCHAR(10),
12> last_name VARCHAR(10),
13> salary DECIMAL(10,2),
14> start_Date DATETIME,
15> region VARCHAR(10),
16> city VARCHAR(20)
17> );
18> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver");
2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown");
3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris");
4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London");
5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton");
6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary");
7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York");
8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina");
9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto");
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select * from employee;
2> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected)
1>
2> CREATE TABLE title(
3> id INTEGER,
4> job_title VARCHAR(20)
5> );
6> GO
1> INSERT INTO title VALUES (1, "developer");
2> INSERT INTO title VALUES (1, "manager");
3> INSERT INTO title VALUES (2, "tester");
4> INSERT INTO title VALUES (2, "programmer");
5> INSERT INTO title VALUES (3, "boss");
6> INSERT INTO title VALUES (4, "sales");
7> INSERT INTO title VALUES (5, "market");
8> INSERT INTO title VALUES (6, "coder");
9> INSERT INTO title VALUES (7, "tester");
10> INSERT INTO title VALUES (8, "developer");
11> INSERT INTO title VALUES (9, "manager");
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT ID, First_Name, Last_Name
4> FROM Employee
5> WHERE EXISTS
6> (SELECT * FROM Title
7> WHERE ID = 1)
8>
9> drop table employee;
10> drop table title;
11> GO
ID First_Name Last_Name
----------- ---------- ----------
1 Jason Martin
2 Alison Mathews
3 James Smith
4 Celia Rice
5 Robert Black
6 Linda Green
7 David Larry
8 James Cat
9 Joan Act
(9 rows affected)
1>
IF EXISTS
3>
4> CREATE PROCEDURE spTableExists
5> @TableName VarChar(128)
6> AS
7> IF EXISTS(SELECT * FROM sysobjects WHERE name = @TableName)
8> PRINT @TableName + " exists"
9> GO
1>
2>
3> drop proc spTableExists;
4> GO
1>
2>
5> IF OBJECT_ID("dbo.Sessions") IS NOT NULL
6> DROP TABLE dbo.Sessions;
7> GO
1>
2> CREATE TABLE dbo.Sessions
3> (
4> keycol INT NOT NULL IDENTITY PRIMARY KEY,
5> app VARCHAR(10) NOT NULL,
6> usr VARCHAR(10) NOT NULL,
7> starttime DATETIME NOT NULL,
8> endtime DATETIME NOT NULL,
9> CHECK(endtime > starttime)
10> );
11> GO
1>
2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 08:30", "20060212 10:30");
3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 08:30", "20060212 08:45");
4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:00", "20060212 09:30");
5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 09:15", "20060212 10:30");
6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:15", "20060212 09:30");
7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 10:30", "20060212 14:30");
8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 10:45", "20060212 11:30");
9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 11:00", "20060212 12:30");
10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 08:30", "20060212 08:45");
11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 09:00", "20060212 09:30");
12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 11:45", "20060212 12:00");
13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 12:30", "20060212 14:00");
14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 12:45", "20060212 13:30");
15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 13:00", "20060212 14:00");
16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 14:00", "20060212 16:30");
17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 15:30", "20060212 17:00");
18> GO
1>
2> SELECT DISTINCT app, usr, starttime AS s
3> FROM dbo.Sessions AS O
4> WHERE NOT EXISTS
5> (SELECT * FROM dbo.Sessions AS I
6> WHERE I.app = O.app
7> AND I.usr = O.usr
8> AND O.starttime > I.starttime
9> AND O.starttime <= I.endtime);
10> GO
app usr s
---------- ---------- -----------------------
app1 user1 2006-02-12 08:30:00.000
app1 user1 2006-02-12 10:45:00.000
app1 user2 2006-02-12 08:30:00.000
app1 user2 2006-02-12 09:15:00.000
app2 user1 2006-02-12 08:30:00.000
app2 user1 2006-02-12 11:45:00.000
app2 user1 2006-02-12 12:45:00.000
app2 user1 2006-02-12 14:00:00.000
app2 user2 2006-02-12 09:00:00.000
app2 user2 2006-02-12 12:30:00.000
app2 user2 2006-02-12 15:30:00.000
1>
2> drop table sessions;
3> GO
The syntax of a subquery that uses the EXISTS operator
WHERE [NOT] EXISTS (subquery)
use the EXISTS operator to test that one or more rows are returned by the subquery.
When you use the EXISTS operator with a subquery, the subquery doesn"t actually return any rows.
Instead, it returns an indication of whether any rows meet the specified condition.
11>
12>
13>
14> create table Billings (
15> BankerID INTEGER,
16> BillingNumber INTEGER,
17> BillingDate datetime,
18> BillingTotal INTEGER,
19> TermsID INTEGER,
20> BillingDueDate datetime ,
21> PaymentTotal INTEGER,
22> CreditTotal INTEGER
23>
24> );
25> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3>
4> create table Bankers(
5> BankerID Integer,
6> BankerName VARCHAR(20),
7> BankerContactLName VARCHAR(20),
8> BankerContactFName VARCHAR(20),
9> BankerCity VARCHAR(20),
10> BankerState VARCHAR(20),
11> BankerZipCode VARCHAR(20),
12> BankerPhone VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4> SELECT BankerID, BankerName, BankerState
5> FROM Bankers
6> WHERE NOT EXISTS
7> (SELECT *
8> FROM Billings
9> WHERE Billings.BankerID = Bankers.BankerID)
10> GO
BankerID BankerName BankerState
----------- -------------------- --------------------
(0 rows affected)
1>
2> drop table Billings;
3> drop table Bankers;
4> GO