MySQL – Using the JOIN Operator Within a SELECT Statement
Many times while working with a MySQL database you will have data spread over multiple tables. What happens if you need to access information from multiple tables within one query? In MySQL a JOIN query is just the statement for the job.
Its actually quite simple to do this. Let’s work through a simple example.
Suppose you were working with a website that sells flower arrangements. Lets say you have a table called flowers which contains two fields: ID, flower_name This table may contain flowers like: carnations and roses. Let’s also assume that you have a second table called colors which includes two fields: flower_id, color Since a roses come in many different colors the person who is placing the order on the website will need to be able to select which color he/she wants.
If you were to do a standard SELECT query on the flowers table you wouldn’t know which colors were available.
SELECT * FROM flowers
The above query would simply return all of the flower name and their associated id numbers. As you can see I placed the select statement on several lines. The reason I do this is to make it easier to read. Sure in this simple example it may not seem logical, but when you start building larger and more complex query statements it make it a lot easier when you return to your code at a later time, or if someone else has to read your code.
Let’s take a look at how to setup the query to JOIN both the flowers and colors table:
SELECT * FROM flowers JOIN colors ON colors.flower_ID=flowers.ID
Let’s break this down. We start off easily by selecting every record within the flowers table. Next after the JOIN operator, we state which table we want to join with. In this case its the “colors” table. Next comes the most difficult part…After the ON operator, we state which columns the tables should join. Since every color has a reference to a flower by the “flower_ID” column within the colors table and every flower has an ID within the flowers table The ID is the field which links the records within each table together.
The final piece to note is if you use the WHERE operand within a JOIN query statement, you need to add one additional piece of information, and that is the table name. Using our previous example, lets say you wanted to select all of the records WHERE ID=2 This would break the query because MySQL doesn’t know which ID you’re talking about. Both tables have a column ID. To do this correctly, you must specify which table you are referring to in your query. See the example below:
SELECT * FROM flowers JOIN colors ON colors.flower_ID=flowers.ID WHERE flowers.ID=2
Again, you will see in the WHERE operand the table name flowers was referenced first, followed by the field within that table which you want to reference, followed by the value of that field. The above query would return all of the colors associated with the flower which has an ID of 2
That’s all there is to a MySQL JOIN query statement!
BUT, let say you are working with some really long table names and you wanted to abbreviate them. Well, you can easily do that by simply injecting the AS operand into the JOIN query statement. Let use the same query we just worked through, but notice the difference.
SELECT * FROM flowers AS fl JOIN colors AS cl ON cl.flower_ID=fl.ID WHERE flowers.ID=2
All we did was simply abbreviated the table colors to cl and the table flowers to fl When working with really long table names this comes in quite handy.