Home arrow MySQL arrow MySQL Table Joins

MySQL Table Joins

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
One of the great characteristics of SQL (Structured Query Language) is the fact that one can develop access and modify data across various tables. There are several benefits to this, including greater ease of manipulation, increased speed of access, and reduced data redundancy. In MySQL (as well as many other SQL languages), this is accomplished via the join command.

So what does a join do, anyway? In short, a join command unites some or all of the data from two or more tables into one comprehensive structure. If this capability was not available, then the administrator would be forced to create ever-expanding, all-encompassing tables that would result in the reduction of overall efficiency and speed of data access and manipulation, even eventually possibly causing the database structure to fail entirely. The joining capability allows the administrator to create small, compact relational tables that can greatly influence database performance. This is especially useful among larger organizations, given the fact that they have the opportunity to divide their database into several departmental sections, allowing each department to effectively manage their relevant data. The alternative to this would be to force one administrator to manage all departmental sections, something that probably does not bode well both in terms of overall organization and efficiency.

For sake of illustration, let's assume a computer manufacturer is in need of a database in which it can efficiently mine data. This data will be divided into various compact tables, each specifying data relative to a certain concept, such as orders, clients and products. After the tables are created, various examples with be illustrated, showing how this manufacturer can use joins to make the most of the database.

(Note to reader: While the concepts to follow could be applied to practically all SQL-based databases, it should be noted that the examples are illustrated using MySQL syntax. Therefore, there could be minor differences between this syntax and other sql-based languages.)

The first table will hold the various types of PCs that the manufacturer distributes:


mysql> create table pcs (     -> pid INT, // product id     -> spec char(3),     -> os char(10),     -> ram INT,     -> hd char(4)     -> );
The second table will contain data regarding the various clients of the manufacturer:


mysql> create table clients (     -> name char(25),     -> cid char(8), // client id     -> email char(25),     -> tel char(10)     -> );

The third table will contain data regarding order information:


mysql> create table orders (     -> order_date date,     -> pid INT,     -> cid char(8)     -> );

Data Insertion

We next insert data into each table:

pcs
 
pidspecosramhd
1 386 linux 64 3.1
2 386 linux 128 4.2
3 486 WinNT 64 3.1
4 586 Linux 128 4.2
5 586 Win98 128 6.4

clients
 
namecidemailtel
acme, inc. acm-042 acme@acme.com 123-456-7890
widgets-r-us, inc. wig-043 widgets@rus.com 421-555-3434
italimp, inc. imp-042 italian@imports.com 459-555-3212
fedey, inc. fed-043 fed@ey.com 439-555-8899

orders
 
datepidcid
1999-12-05 2 acm-042
1999-12-04 3 wig-043
1999-12-04 1 acm-042
1999-12-05 2 acm-042
1999-12-12 5 fed-043
1999-12-05 5 imp-042


So, how does one go about pulling useful data from these tables? What if the boss wanted a report listing the email addresses of all clients who ordered a particular pc on a particular date? Or perhaps the amount of RAM used in filling orders placed by a particular client? This is easily accomplished via the various joins available to us. Let's use the tables we have created and construct our first join statement.



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