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