Sams Teach Yourself Microsoft SQL Server 7 in 21 Days

By Richard Waymire and Rick Sawtell

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part 1: Week 1 At a Glance
  • Day 1. Introduction to SQL Server 7.0 and Relational Databases
  • Day 2. Installing SQL Server 7.0
  • Day 3. Using the SQL Server 7.0 Tools and Utilities
  • Day 4. Creating and Implementing Databases, Files, and Filegroups
  • Day 5. Using SQL Server Login and User Security
  • Day 6. Working with Permissions
  • Day 7. Implementing Backups in SQL Server 7.0
  • Part 2: Week 2 At a Glance
  • Day 8. Restoring SQL Server Databases
  • Day 9. Creating Tables
  • Day 10. Using SQL Server 7.0 Data Transformation Services
  • Day 11. Retrieving Data with Queries
    • Simple SELECT Statements
    • Manipulating Data
    • System Functions
    • Data Conversion
    • Choosing Rows
    • Eliminating Duplicate Information
    • Sorting Data Using the ORDER BY Clause
    • Midchapter Review
    • Aggregate Functions
    • Super Aggregates (ROLLUP and CUBE)
    • Data Correlation
    • Subqueries
    • Correlated Subqueries
    • SELECT INTO
    • UNION Operator
    • Summary
    • Q&A
    • Workshop
  • Day 12. Data Modification with Queries
  • Day 13. Enhancing Performance with Indexing
  • Day 14. Ensuring Data Integrity
  • Part 3: Week 3 At a Glance
  • Day 15. Working with Views, Stored Procedures, and Triggers
  • Day 16. Programming SQL Server 7.0
  • Day 17. Understanding Replication Design and Methodologies
  • Day 18. Implementing Replication Methodologies
  • Day 19. Using the SQL Server Agent
  • Day 20. Configuring and Monitoring SQL Server 7.0
  • Day 21. Integrating SQL Server and the World Wide Web
  • Appendix A. Answers

Correlated Subqueries

A correlated subquery references a table from the outer query and evaluates each row for the outer query. In this aspect, a correlated subquery differs from a normal subquery because the subquery depends on values from the outer query. A normal subquery is executed independently of the outer query.

In the following example, the join query is rewritten as a correlated subquery. The queries will return the same information. The queries answer the following instruction: Show me authors who live in the same city and zip code.

SQL Server JOIN Syntax

   SELECT au1.au_fname, au1.au_lname,

   au2.au_fname, au2.au_lname,

   au1.city, au1.zip

   FROM authors au1, authors au2

   WHERE au1.city = au2.city

   AND au1.zip = au2.zip

   AND au1.au_id < au2.au_id

   ORDER BY au1.city, au1.zip

au_fname    au_lname    au_fname    au_lname    city    zip
-------    -------    -------    -------    ----------    ------
Cheryl    Carson    Abraham    Bennet    Berkeley    94705
Dean    Straight    Dirk    Stringer    Oakland    94609
Dean    Straight    Livia    Karsen    Oakland    94609
Dirk    Stringer    Livia    Karsen    Oakland    94609
Ann    Dull    Sheryl    Hunter    Palo Alto    94301
Anne    Ringer    Albert    Ringer    Salt Lake City    84152

(6 row(s) affected)

SQL Server Correlated Subquery Syntax

   SELECT au1.au_fname, au1.au_lname, au1.city, au1.zip

   FROM authors au1

   WHERE zip IN

   (SELECT zip

   FROM authors au2

   WHERE au1.city = au2.city

   AND au1.au_id <> au2.au_id)

   ORDER BY au1.city, au1.zip

au_fname    au_lname    city    zip
--------    ------------    ---------------    ---------
Abraham    Bennet    Berkeley    94705
Cheryl    Carson    Berkeley    94705
Livia    Karsen    Oakland    94609
Dirk    Stringer    Oakland    94609
Dean    Straight    Oakland    94609
Sheryl    Hunter    Palo Alto    94301
Ann    Dull    Palo Alto    94301
Albert    Ringer    Salt Lake City    84152
Anne    Ringer    Salt Lake City    84152

(9 row(s) affected)

Notice that the same data is returned; it's just formatted differently and is more readable.

Share This