The Left Join - MySQL

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.

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

blog comments powered by Disqus