Complex joins

In the previous example we have a one-to-one relationship between our data - one golf centre ID matches precisely one golf centre name. However, things are rarely this neat in the real world - all too often a client has two account managers, or, on the other hand, an account manager might not have an account right now. How do you craft a query that means "Select all account managers and the names of their accounts, excluding the ones that have no accounts currently"? To do that you need to investigate the world of complex joins, which has a microcosmic jargon of its own including terms such as Cartesian product, left, right, and full inner join, and left, right, and full outer join - a bit too much for this book!

However, suffice to say that difference types of joins are important. Right outer joins, for example, allow you to select all rows from table A where a field matches rows from table B, as well as any unmatched rows from table B. Left outer joins are similar, with the difference of returning any unmatched rows from table A. Full outer joins are also similar, with the difference of returning any unmatched rows from table A or table B - see the pattern?

As I said, this is not really the place for such in-depth discussion of database querying - generally this level of detail is only covered in books specifically on databases, which pretty much confirms that only a very few need to know how to use it all!

 

Next chapter: Using temporary tables >>

Previous chapter: Table joins

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar