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!

 

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Using temporary tables >>

Previous chapter: Table joins

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.