NOT EXISTS with correlated subquery : Exists « Query « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Query
  3. Exists
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








1.10.Exists
1.10.1.The syntax of a subquery that uses the EXISTS operator
1.10.2.EXISTS() returns a row in the outer query when any records are returned by a subquery.
1.10.3.A simple subquery using the EXISTS clause.
1.10.4.NOT EXISTS with correlated subquery
1.10.5.IF EXISTS
1.10.6.A common use for EXISTS is to answer a query such as Show me the titles for which no stores have sales.