The Equi-join - MySQL

The equi-join is performed when certain values contained in one table are matched with values contained within a second (or multiple) table(s).

Let's assume that we want a list of clients who ordered a pc with the product id 1:

mysql> select p.os, c.name from orders o, pcs p, clients c 
where p.pid=o.pid and o.pid = 1 and o.cid=c.cid";

os	name
linux	acme, Inc.

The Non-Equi-Join

The equi-join works great when we want to match corresponding data between multiple tables. But what if there was a need to display data according to their characteristic of inequality? For example, the boss requires a list of all operating systems (os) and their corresponding order id numbers where the order pid is greater than the product pid. The solution? The aptly named non-equi join.

mysql> SELECT p.os, o.pid from orders o, pcs p where o.pid > p.pid;

os	pid
linux	2
linux	3
linux	2
linux	5
linux	5
linux	3
linux	5
linux	5
WinNT	5
WinNT	5
linux	5
linux	5

This matches all rows where the order table pid is greater than the product table pid. Look carefully, as you will realize that this is simply a cross-join with various restrictions placed upon it. While this may not be exceptionally useful to the boss, it does set the stage for a very useful function, the left join. Let's move on, concentrating upon the various options available when using the left join.

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

blog comments powered by Disqus