MySQL Table Joins - The Left Join (
Page 4 of 5 )
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_date |
pid |
cid |
pid |
spec |
os |
ram |
hd |
| 1999-12-05 |
2 |
acm-042 |
2 |
386 |
linux |
128 |
4.2 |
| 1999-12-04 |
3 |
wig-043 |
3 |
486 |
WinNT |
64 |
3.1 |
| 1999-12-04 |
1 |
acm-042 |
1 |
386 |
linux |
64 |
3.1 |
| 1999-12-05 |
2 |
acm-042 |
2 |
386 |
linux |
128 |
4.2 |
| 1999-12-12 |
5 |
fed-043 |
5 |
586 |
Win98 |
128 |
6.4 |
| 1999-12-05 |
5 |
imp-042 |
5 |
586 |
Win98 |
128 |
6.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_date |
pid |
cid |
pid |
spec |
os |
ram |
hd |
| 1999-12-05 |
2 |
acm-042 |
2 |
null |
null |
null |
null |
| 1999-12-04 |
3 |
wig-043 |
3 |
486 |
WinNT |
64 |
3.1 |
| 1999-12-04 |
1 |
acm-042 |
1 |
null |
null |
null |
null |
| 1999-12-05 |
2 |
acm-042 |
2 |
null |
null |
null |
null |
| 1999-12-12 |
5 |
fed-043 |
5 |
null |
null |
null |
null |
| 1999-12-05 |
5 |
imp-042 |
5 |
null |
null |
null |
null |
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:
| name |
cid |
email |
tel |
order_date |
pid |
cid |
| acme, inc. |
acm-042 |
acme@acme.com |
123-465-789 |
1999-12-05 |
2 |
acm-042 |
| acme, inc. |
acm-042 |
acme@acme.com |
123-465-789 |
1999-12-04 |
1 |
acm-042 |
| acme, inc. |
acm-042 |
acme@acme.com |
123-465-789 |
1999-12-05 |
2 |
acm-042 |
| widgets-r-us, inc. |
wig-043 |
widgets@rus.com |
421-555-3434 |
1999-12-04 |
3 |
wig-043 |
| italimp, inc. |
imp-042 |
widgets@rus.com |
459-555-3434 |
1999-12-05 |
5 |
imp-042 |
| fedey, inc. |
fed-043 |
fed@ey.com |
439-555-8899 |
1999-12-12 |
5 |
fed-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.
/ 108 
