Home arrow MySQL arrow Page 5 - Understanding SQL Joins

Finding Common Ground - 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
Since a cross join produces a huge resultset, it is considered a good idea to attach a WHERE clause to the join to filter out some of the records. Here's an example:

SELECT * FROM a,b WHERE a1 > 30;
This returns

+----+------+----+------+ | a1 | a2 | b1 | b2 | +----+------+----+------+ | 40 | x | 10 | p | | 50 | y | 10 | p | | 60 | z | 10 | p | | 40 | x | 20 | q | | 50 | y | 20 | q | | 60 | z | 20 | q | +----+------+----+------+ 6 rows in set (0.06 sec)
A variant of the cross join is the "equi-join", where certain fields in the joined tables are equated to each other. In this case, the final resultset will only include those rows from the joined tables which have matches in the specified fields.

SELECT * FROM a,b WHERE a1 = b1;
This returns

+----+------+----+------+ | a1 | a2 | b1 | b2 | +----+------+----+------+ | 10 | u | 10 | p | | 20 | v | 20 | q | +----+------+----+------+ 2 rows in set (0.00 sec)
Here, too, you can use a WHERE clause to further narrow the resultset:

SELECT * FROM a,b WHERE a1 = b1 AND a1 = 20;
This returns

+----+------+----+------+ | a1 | a2 | b1 | b2 | +----+------+----+------+ | 20 | v | 20 | q | +----+------+----+------+ 1 row in set (0.00 sec)


 
 
>>> 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: