Home arrow MySQL arrow Page 8 - Understanding SQL Joins

The Bookworm Turns - MySQL

Left join. Right join. Inner join. If you've ever wondered what all this jargon means, it's time to find out. Welcome to the wild, the wacky, the insanely cool world of SQL joins.

TABLE OF CONTENTS:
  1. Understanding SQL Joins
  2. Meeting The Family
  3. Keeping It Simple
  4. Crossed Wires
  5. Finding Common Ground
  6. One Step Left...
  7. ...Two Steps Right
  8. The Bookworm Turns
  9. Up A Tree
  10. A Long Goodbye
By: The Disenchanted Developer, (c) Melonfire
Rating: starstarstarstarstar / 247
August 20, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
So that's the theory - now let's see how it plays out in real life. Consider the following tables, which contain data for a small lending library:

+----+-------------------------------------+ | id | title | +----+-------------------------------------+ | 1 | Mystic River | | 2 | Catch-22 | | 3 | Harry Potter and the Goblet of Fire | | 4 | The Last Detective | | 5 | Murder on the Orient Express | +----+-------------------------------------+
This table, named "books", contains a complete list of books available for lending. Each book has a unique ID.

+-----+-------+ | id | name | +-----+-------+ | 100 | Joe | | 101 | John | | 103 | Dave | | 109 | Harry | | 110 | Mark | | 113 | Jack | +-----+-------+
This table, named "members", contains a list of all the members of the library. Like the books, each member also has a unique ID.

+-----------+---------+ | member_id | book_id | +-----------+---------+ | 101 | 1 | | 103 | 3 | | 109 | 4 | +-----------+---------+
As members borrow books from the library, the "data" table, above, is updated with information on which book has been lent to which member.

Using these tables and whatever you've just learned about joins, it's possible to write queries that answer the most common questions asked of the system by its operators. For example, let's suppose I wanted a list of all the current members:

SELECT * FROM members; +-----+-------+ | id | name | +-----+-------+ | 100 | Joe | | 101 | John | | 103 | Dave | | 109 | Harry | | 110 | Mark | | 113 | Jack | +-----+-------+
How about a list of all the books currently in the library catalog?

SELECT * FROM books; +----+-------------------------------------+ | id | title | +----+-------------------------------------+ | 1 | Mystic River | | 2 | Catch-22 | | 3 | Harry Potter and the Goblet of Fire | | 4 | The Last Detective | | 5 | Murder on the Orient Express | +----+-------------------------------------+
How about an enquiry on Dave's account, to see which books he's currently checked out?

SELECT books.title FROM data,books WHERE data.book_id = books.id AND data.member_id = 103; +-------------------------------------+ | title | +-------------------------------------+ | Harry Potter and the Goblet of Fire | +-------------------------------------+
Can we find out which members *don't* have any books checked out to them (maybe these are inactive accounts)?

SELECT members.id, members.name FROM members LEFT JOIN data ON data.member_id = members.id WHERE data.book_id IS NULL; +-----+------+ | id | name | +-----+------+ | 100 | Joe | | 110 | Mark | | 113 | Jack | +-----+------+
How about a list of all the books that are currently in stock (not checked out to anyone)?

SELECT books.title FROM books LEFT JOIN data ON data.book_id = books.id WHERE data.book_id IS NULL +------------------------------+ | title | +------------------------------+ | Catch-22 | | Murder on the Orient Express | +------------------------------+
Now, how about a list of all the books currently checked out, together with the name of the member they've been checked out to?

SELECT members.name, books.title FROM data, members, books WHERE data.member_id = members.id AND data.book_id = books.id; +-------+-------------------------------------+ | name | title | +-------+-------------------------------------+ | John | Mystic River | | Dave | Harry Potter and the Goblet of Fire | | Harry | The Last Detective | +-------+-------------------------------------+
You could also accomplish the above with the following query (an outer join instead of an inner join):

SELECT members.name, books.title FROM books,members LEFT JOIN data ON data.book_id = books.id WHERE data.member_id = members.id AND data.book_id IS NOT NULL;
As you can see, joins allow you to extract all kinds of different information from a set of tables. Go ahead and try it for yourself - think of a question you would like to ask this system, and then write a query to answer it.

 
 
>>> More MySQL Articles          >>> More By The Disenchanted Developer, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: