Home arrow MySQL arrow Page 3 - MySQL Table Joins

The Equi-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 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";

osname
linuxacme, 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;

ospid
linux2
linux3
linux2
linux5
linux5
linux3
linux5
linux5
WinNT5
WinNT5
linux5
linux5

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
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: