Home arrow MySQL arrow Page 6 - Understanding SQL Joins

One Step Left... - 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
Let's move on to outer joins. You'll remember, from a few pages back, that inner joins are symmetrical - in order to be included in the final resultset, rows must match in all joined tables. Outer joins, on the other hand, are asymmetrical - all rows from one side of the join are included in the final resultset, regardless of whether or not they match rows on the other side of the join.

This might seem a little complicated, but an example should soon clear that up. Consider the following SQL query:

SELECT * FROM a LEFT JOIN b ON a1 = b1;
In English, this translates to "select all the rows from the left side of the join (table a) and, for each row selected, either display the matching value from the right side (table b) or display an empty row". This kind of join is known as a "left join" or, sometimes, a "left outer join".

Here's the result:

+----+------+------+------+ | a1 | a2 | b1 | b2 | +----+------+------+------+ | 10 | u | 10 | p | | 20 | v | 20 | q | | 30 | w | NULL | NULL | | 40 | x | NULL | NULL | | 50 | y | NULL | NULL | | 60 | z | NULL | NULL | +----+------+------+------+ 6 rows in set (0.06 sec)
As you can see, all the rows from the left side of the join - table "a" - appear in the final resultset. Those which have a corresponding value on the right side - table "b" - as per the match criteria a1 = b1 have that value displayed; the rest have a null row displayed.

This kind of join comes in very handy when you need to see which values from one table are missing in another table - all you need to do is look for the null rows. Just from a quick glance at the example above, it's fairly easy to see that rows 30-60 are present in table "a", but absent in table "b". This technique also comes in handy when you're looking for corrupted, or "dirty", data.

In fact, you can even save your eyeballs the trouble of scanning the output - just let SQL do it for you, with an additional WHERE clause:

SELECT a1 FROM a LEFT JOIN b ON a1 = b1 WHERE b1 IS NULL;
Here's the output:

+----+ | a1 | +----+ | 30 | | 40 | | 50 | | 60 | +----+ 4 rows in set (0.05 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: