Advanced SQL Programming Fall 2002 Examples using in and exists, Oct. 7th 1> use pubs2 2> go /* */ /* */ /* Find authors who live in the same state as a publisher */ /* -- IN with subquery */ /* */ 1> select au_lname, authors.state 2> from authors 3> where authors.state in 4> (select publishers.state 5> from publishers) 6> go au_lname state ---------------------------------------- ----- White CA Green CA Carson CA O'Leary CA Straight CA Bennet CA Dull CA Gringlesby CA Locksley CA Yokomoto CA Stringer CA MacFeather CA Karsen CA Hunter CA McBadden CA (15 rows affected) /* */ /* Same question as above - join */ /* */ 1> select au_lname, a.state 2> from authors a, publishers p 3> where a.state = p.state 4> go au_lname state ---------------------------------------- ----- White CA Green CA Carson CA O'Leary CA Straight CA Bennet CA Dull CA Gringlesby CA Locksley CA Yokomoto CA Stringer CA MacFeather CA Karsen CA Hunter CA McBadden CA (15 rows affected) /* */ /* Authors who do _not_ live in same state as a publisher - */ /* NOT EXISTS (with subquery) */ /* */ 1> select au_lname, authors.state 2> from authors 3> where not exists (select * 4> from publishers 5> where authors.state = publishers.state) 6> go au_lname state ---------------------------------------- ----- Smith KS Greene TN Blotchet-Halls OR del Castillo MI DeFrance IN Panteley MD Ringer UT Ringer UT (8 rows affected) /* */B /* Quick check on publisher states: */ /* */ 1> select state from publishers 2> go state ----- MA DC CA (3 rows affected) 1> quit