Home arrow MySQL arrow Page 4 - MySQL Table Joins

The Left Join - MySQL

One of the great characteristics of SQL (Structured Query Language) is thefact that onecan develop, access and modify data across various tables. There are severalbenefits tothis, including greater ease of manipulation, increased speed of access, andreduceddata redundancy. In MySQL (as well as many other SQL languages), this isaccomplished via the join command.

TABLE OF CONTENTS:
  1. MySQL Table Joins
  2. The Cross Join
  3. The Equi-join
  4. The Left Join
  5. Self-joins
By: W.J. Gilmore
Rating: starstarstarstarstar / 150
July 06, 1999

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
The left join allows the user to pull out all sorts of interesting data based upon certain restrictions. This is an extremely powerful option of table joins, and greatly facilitates table manipulation.

Now, the boss is screaming for some details. Details, details, details! It's no problem however, as the left join will solve our problem.

Assume that


mysql> select * from orders left join pcs on orders.pid = pcs.pid";

order_datepidcidpidspecosramhd
1999-12-052acm-0422386linux1284.2
1999-12-043wig-0433486WinNT643.1
1999-12-041acm-0421386linux643.1
1999-12-052acm-0422386linux1284.2
1999-12-125fed-0435586Win981286.4
1999-12-055imp-0425586Win981286.4

We now have a informative listing of all pcs ordered by our clients! Using a PHP3 or Perl script, one could see how this could be used to print out receipts, for example. We could combine this with the client table for reason of emailing the client an occasional email with a list of all products he has purchased from our company.

Perhaps another useful report we could generate would involve learning of the number of pcs ordered that had product id (pid) number 3.


mysql> select * from orders left join pcs on pcs.pid = 3 and orders.pid = pcs.pid;

order_datepidcidpidspecosramhd
1999-12-052acm-0422nullnullnullnull
1999-12-043wig-0433486WinNT643.1
1999-12-041acm-0421nullnullnullnull
1999-12-052acm-0422nullnullnullnull
1999-12-125fed-0435nullnullnullnull
1999-12-055imp-0425nullnullnullnull

The Using Clause

A variation to the left join allows us to further correlate identical columns residing in multiple tables. This is the using option. Instead of the following:


mysql> SELECT * from clients join on orders where clients.cid = orders.cid;

We could state:


mysql> SELECT * from clients join on orders using (cid);

However, both would produce the same result:

namecidemailtelorder_datepidcid
acme, inc.acm-042acme@acme.com123-465-7891999-12-052acm-042
acme, inc.acm-042acme@acme.com123-465-7891999-12-041acm-042
acme, inc.acm-042acme@acme.com123-465-7891999-12-052acm-042
widgets-r-us, inc.wig-043widgets@rus.com421-555-34341999-12-043wig-043
italimp, inc.imp-042widgets@rus.com459-555-34341999-12-055imp-042
fedey, inc.fed-043fed@ey.com439-555-88991999-12-125fed-043

There you have it. Table joins made easy. Try playing around with variations of the commands highlighted within this article to gain a clear understanding of the syntax. Once this is understood, you will find that table joins will play an integral part in your development activities. Be sure to check out MySQL's various discussion groups (http://www.mysql.com), as there is usually quite a bit of information exchanged regarding table joins.



 
 
>>> More MySQL Articles          >>> More By W.J. Gilmore
 

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: