Home arrow MySQL arrow Page 2 - MySQL Table Joins

The Cross 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 cross-join is a very basic type of join that simply matches each row from one table to every row from another table. While not very efficient, it does illustrate the common characteristic of all joins: table unification.

An example of a cross-join would be as follows:


mysql> SELECT * FROM pcs, clients;

So why are there so many rows? Remember that a cross-join matches up each row of one table with every row of the second table. Therefore, a join involving two tables in which one table has 3 rows and the second table has 4 rows would result in 12 rows.

Perhaps it is easy to remember the cross-join by putting the result in the perspective that each row in table1 crosses every row in table 2 once.

Voilą! You have successfully performed your first join! Not so hard after all, is it?

Now try the following and see what happens:


mysql> select c.name, o.cid from orders o, clients c where o.cid = "acm-042";

namecid
acme, Inc.acm-042
acme, Inc.acm-042
acme, Inc.acm-042
widgets-r-us, inc.acm-042
widgets-r-us, inc.acm-042
widgets-r-us, inc.acm-042
italimp, inc.acm-042
italimp, inc.acm-042
italimp, inc.acm-042
fedey, inc.acm-042
fedey, inc.acm-042
fedey, inc.acm-042

So what did you think about the result? Was it what you expected it to be? This time, each name in table clients was matched to every row in orders containing "acm-042". It still fits our above explanation of a cross-join, yet it is more selective in which rows are "crossed". Try playing around with the order of the query, and see what happens. Notice that when the table order is switched, the output will change accordingly.

Note: Why are aliases used when naming tables? Aliases are used as a method of saving repetitious keystrokes when entering queries. Thus, instead of repeatedly spelling out 'clients' when specifying columns, we could use 'c' and then state 'from clients c' within our query.

Although the cross-join does in fact bring our tables together, it is not very efficient. So how can we sparingly choose the data we want from each table? Read on to find out more.



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