Home arrow MySQL arrow Page 4 - Understanding SQL Joins

Crossed Wires - 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
The examples on the previous page dealt with a single table, so the question of a join never arose. But often, using a single table returns an incomplete picture; you need to combine data from two or more tables in order to see a more accurate result.

That's where joins come in - they allow you to combine two or more tables, and to massage the data within those tables, in a variety of different ways. For example,

SELECT * FROM a,b;
Here's the output:

+----+------+----+------+ | a1 | a2 | b1 | b2 | +----+------+----+------+ | 10 | u | 10 | p | | 20 | v | 10 | p | | 30 | w | 10 | p | | 40 | x | 10 | p | | 50 | y | 10 | p | | 60 | z | 10 | p | | 10 | u | 20 | q | | 20 | v | 20 | q | | 30 | w | 20 | q | | 40 | x | 20 | q | | 50 | y | 20 | q | | 60 | z | 20 | q | +----+------+----+------+ 12 rows in set (0.00 sec)
And that's your very first join!

In this case, columns from both tables are combined to produce a resultset containing all possible combinations. This kind of join is referred to as a "cross join", and the number of rows in the joined table will be equal to the product of the number of rows in each of the tables used in the join. You can see this from the example above - table "a" has 6 rows, table "b" has 2 rows, and so the joined table has 6x2 = 12 rows.

There are two basic types of SQL joins - the "inner join" and the "outer join". Inner joins are the most common - the one you just saw was an example of an inner join - and also the most symmetrical, since they require a match in each table which forms a part of the join. Rows which do not match are excluded from the final resultset.

As you might imagine, a cross join like the one above can have huge implications for the performance of your database server. In order to illustrate, look what happens when I add a third table to the join above:

SELECT * FROM a,b,c;
Here's the output:

+----+------+----+------+-----+------+ | a1 | a2 | b1 | b2 | c1 | c2 | +----+------+----+------+-----+------+ | 10 | u | 10 | p | 90 | m | | 20 | v | 10 | p | 90 | m | | 30 | w | 10 | p | 90 | m | | 40 | x | 10 | p | 90 | m | | 50 | y | 10 | p | 90 | m | | 60 | z | 10 | p | 90 | m | | 10 | u | 20 | q | 90 | m | | 20 | v | 20 | q | 90 | m | | 30 | w | 20 | q | 90 | m | | 40 | x | 20 | q | 90 | m | | 50 | y | 20 | q | 90 | m | | 60 | z | 20 | q | 90 | m | | 10 | u | 10 | p | 100 | n | | 20 | v | 10 | p | 100 | n | | 30 | w | 10 | p | 100 | n | | 40 | x | 10 | p | 100 | n | | 50 | y | 10 | p | 100 | n | | 60 | z | 10 | p | 100 | n | | 10 | u | 20 | q | 100 | n | | 20 | v | 20 | q | 100 | n | | 30 | w | 20 | q | 100 | n | | 40 | x | 20 | q | 100 | n | | 50 | y | 20 | q | 100 | n | | 60 | z | 20 | q | 100 | n | | 10 | u | 10 | p | 110 | o | | 20 | v | 10 | p | 110 | o | | 30 | w | 10 | p | 110 | o | | 40 | x | 10 | p | 110 | o | | 50 | y | 10 | p | 110 | o | | 60 | z | 10 | p | 110 | o | | 10 | u | 20 | q | 110 | o | | 20 | v | 20 | q | 110 | o | | 30 | w | 20 | q | 110 | o | | 40 | x | 20 | q | 110 | o | | 50 | y | 20 | q | 110 | o | | 60 | z | 20 | q | 110 | o | +----+------+----+------+-----+------+ 36 rows in set (0.05 sec)
Though each of the tables used in the join contains less than ten records each, the final joined result contains 6x2x3=36 records. This might not seem like a big deal when all you're dealing with are three tables containing a total of 11 records, but imagine what would happen if you had three tables, each containing 100 records, and you decided to cross join them...

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