SQL Server/T-SQL Tutorial/Query/ANY — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
ANY and ALL Operators
The operators ANY and ALL are always used in combination with one of the comparison operators.
The general syntax of both operators is
column operator [ANY | ALL] query
where operator stands for a comparison operator.
The ANY operator evaluates to true if the result of an inner query contains at least one row that satisfies the comparison.
12>
13>
14> CREATE TABLE employee(
15> id INTEGER NOT NULL PRIMARY KEY,
16> first_name VARCHAR(10),
17> last_name VARCHAR(10),
18> salary DECIMAL(10,2),
19> start_Date DATETIME,
20> region VARCHAR(10),
21> city VARCHAR(20)
22> );
23> 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 * FROM employee
4> WHERE id > ANY
5> (SELECT id
6> FROM title)
7> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
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
(8 rows affected)
1>
2> drop table employee;
3> drop table title;
4> GO
A query that returns Billings smaller than the largest Billing for Banker 115
7> create table Billings (
8> BankerID INTEGER,
9> BillingNumber INTEGER,
10> BillingDate datetime,
11> BillingTotal INTEGER,
12> TermsID INTEGER,
13> BillingDueDate datetime ,
14> PaymentTotal INTEGER,
15> CreditTotal INTEGER
16>
17> );
18> 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>
2>
3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
4> GO
(1 rows affected)
1>
2>
3>
4>
5>
6> create table Bankers(
7> BankerID Integer,
8> BankerName VARCHAR(20),
9> BankerContactLName VARCHAR(20),
10> BankerContactFName VARCHAR(20),
11> BankerCity VARCHAR(20),
12> BankerState VARCHAR(20),
13> BankerZipCode VARCHAR(20),
14> BankerPhone VARCHAR(20)
15> )
16> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
4> GO
(1 rows affected)
1>
2>
3> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
4> GO
(1 rows affected)
1>
2>
3>
4>
5> SELECT BankerName, BillingNumber, BillingTotal
6> FROM Bankers JOIN Billings ON Bankers.BankerID = Billings.BankerID
7> WHERE BillingTotal < ANY
8> (SELECT BillingTotal
9> FROM Billings
10> WHERE BankerID = 115)
11> GO
BankerName BillingNumber BillingTotal
-------------------- ------------- ------------
(0 rows affected)
1>
2> drop table Billings;
3> drop table Bankers;
4> GO
Finding All Using the ANY Predicate
3>
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT
4> *
5> FROM
6> Employee
7> WHERE
8> id = ANY
9> (
10> SELECT
11> id
12> FROM
13> employee
14> )
15>
16>
17>
18>
19> drop table employee;
20> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
How the ANY and SOME keywords work
Condition Equivalent expression
x > ANY (1, 2) x > 1
x < ANY (1, 2) x < 2
x = ANY (1, 2) (x = 1) OR (x = 2)
x <> ANY (1, 2) (x <> 1) OR (x <> 2)
3>
4>
5>
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> 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> SELECT BankerID, BillingNumber, BillingTotal
4> FROM Billings AS Inv_Main
5> WHERE BillingTotal >
6> (SELECT AVG(BillingTotal)
7> FROM Billings AS Inv_Sub
8> WHERE Inv_Sub.BankerID = Inv_Main.BankerID)
9> ORDER BY BankerID, BillingTotal
10> GO
BankerID BillingNumber BillingTotal
----------- ------------- ------------
(0 rows affected)
1>
2> drop table Billings;
3> drop table Bankers;
4> GO
The use of the ANY operator.
4>
5>
6>
7> CREATE TABLE employee(
8> id INTEGER NOT NULL PRIMARY KEY,
9> first_name VARCHAR(10),
10> last_name VARCHAR(10),
11> salary DECIMAL(10,2),
12> start_Date DATETIME,
13> region VARCHAR(10),
14> city VARCHAR(20)
15> );
16> 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>
2> INSERT INTO title VALUES (6, "coder");
3> INSERT INTO title VALUES (7, "tester");
4> INSERT INTO title VALUES (8, "developer");
5> INSERT INTO title VALUES (9, "manager");
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4> SELECT *
5> FROM employee
6> WHERE id = ANY
7> (SELECT id
8> FROM title
9> WHERE job_title= "tester")
10> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
(1 rows affected)
1>
2>
3> drop table employee;
4> drop table title;
5> GO
This is the query using equivalent formulation with = ANY
5> CREATE TABLE authors(
6> au_id varchar(11),
7> au_lname varchar(40) NOT NULL,
8> au_fname varchar(20) NOT NULL,
9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
10> address varchar(40) NULL,
11> city varchar(20) NULL,
12> state char(2) NULL,
13> zip char(5) NULL,
14> contract bit NOT NULL
15> )
16> GO
1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1)
2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1)
3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1)
6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1)
7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0)
9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1)
11> 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>
2>
3> CREATE TABLE titleauthor(
4> au_id varchar(20),
5> title_id varchar(20),
6> au_ord tinyint NULL,
7> royaltyper int NULL
8> )
9> GO
1>
2> insert titleauthor values("1", "2", 1, 60)
3> insert titleauthor values("2", "3", 1, 100)
4> insert titleauthor values("3", "4", 1, 100)
5> insert titleauthor values("4", "5", 1, 100)
6> insert titleauthor values("5", "6", 1, 100)
7> insert titleauthor values("6", "7", 2, 40)
8> insert titleauthor values("7", "8", 1, 100)
9> insert titleauthor values("8", "9", 1, 100)
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>
3> --Here"s the query using IN:
4>
5> SELECT "Author ID"=A.au_id,
6> "Author"=CONVERT(varchar(20), RTRIM(au_lname) + ", "
7> + RTRIM(au_fname)), state
8> FROM authors A
9> WHERE A.au_id IN
10> (SELECT B.au_id FROM titleauthor B)
11> GO
Author ID Author state
----------- -------------------- -----
1 Joe, Abra CA
2 Jack, Majo CA
3 Pink, Cherry BC
4 Blue, Albert BC
5 Red, Anne SK
6 Black, Michel SK
7 White, Sylvia MD
8 Yellow, Heather CA
(8 rows affected)
1>
2>
3> --This is the query using equivalent formulation with = ANY:
4>
5> SELECT "Author ID"=A.au_id,
6> "Author"=CONVERT(varchar(20), RTRIM(au_lname) + ", "
7> + RTRIM(au_fname)), state
8> FROM authors A
9> WHERE A.au_id=ANY
10> (SELECT B.au_id FROM titleauthor B)
11> GO
Author ID Author state
----------- -------------------- -----
1 Joe, Abra CA
2 Jack, Majo CA
3 Pink, Cherry BC
4 Blue, Albert BC
5 Red, Anne SK
6 Black, Michel SK
7 White, Sylvia MD
8 Yellow, Heather CA
(8 rows affected)
1>
2>
3> drop table authors;
4> drop table titleauthor;
5> GO
1>
2>