Joining Tables in MySQL



Breaking data into multiple tables enables more efficient storage, easier manipulation, and greater scalability. But these benefits come with a price.

If data is stored in multiple tables, how can you retrieve that data with a single SELECT statement?

The answer is to use a join. Simply put, a join is a mechanism used to associate tables within a SELECT statement (and thus the name join). Using a special syntax, multiple tables can be joined so a single set of output is returned, and the join associates the correct rows in each table on-the-fly.

Example on Creating a Join

Creating a join is very simple. You must specify all the tables to be included and how they are related to each other. Look at the following example:

Input
Before Use Join
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));

Using Join
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';


Output

+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

Tip

It Pays to Experiment As you can see, there is often more than one way to perform any given SQL operation. And there is rarely a definitive right or wrong way. Performance can be affected by the type of operation, the amount of data in the tables, whether indexes and keys are present, and a whole slew of other criteria. Therefore, it is often worth experimenting with different selection mechanisms to find the one that works best for you.

Joins are one of the most important and powerful features in SQL, and using them effectively requires a basic understanding of relational database design. In this chapter, you learned some of the basics of relational database design as an introduction to learning about joins. You also learned how to create an equijoin (also known as an inner join), which is the most commonly used form of join. In the next chapter you’ll learn how to create other types of joins.

Book: MySQL Crash Course


Subscribe to Full RSS Feed

RSS FeedIf you thing this article useful, please consider subscribing to our RSS Feed or e-mail updates and stay updated with us. You can also follow @JauhariNET on twitter for latest updates.


About Jauhari

This is me Nurudin Jauhari. Just human like you, I come from Gunungkidul, small place at Yogyakarta, and Today I live at Malang I was born 30/04/1980 in Ponjong, small village in Gunungkidul, Almost my time in 1 - 15 Years stay at this Village. More

This entry was posted in MySQL.
Bookmark the permalink.



One Response to Joining Tables in MySQL

  1. Setu Pitone says:

    Hi sir, i have been reading your articles on your website, and i founded very interesting. so thats why i want you to help me with my database for my project, at Samoa university

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>