Joining Tables with SQL

I recently learned about joining tables with SQL (and using the data in PHP) and it has made database creation and management so easy.

This was something I struggled with understanding on my own until a friend was kind enough to share her code with me and allow me to play around with it.

I’m still new to this, so in no way do I claim to be a PHP expert. I just want to share my knowledge, since using these skills have allowed me to build some pretty cool web apps.

What is the purpose of joining tables?

The ability to join tables makes it easy to combine multiple different tables. The only requirement is that all of the tables being joined share at least one column of data in common. You can join two or more tables!

It’s not always necessary to divide your database into multiple tables. As you continue working with databases, you can usually judge if management would be made easier by splitting things up.

Examples of table data to join

It’s tough to properly explain without an example – so let’s start with a really easy one. Here, we have a database for a webring with three different tables:

  • Websites
  • Owners
  • Website Descriptions

We could just place it all in one table, but sometimes a table with a lot of columns is overwhelming.

Table: website_urls

idwebsite
1https://sadgrl.online/
2https://cinni.net/
3https://auzziejay.com/
Each website is associated with an id. The ID field is auto-incremented.

Table: website_owners

idwebsite_idowner
11Sadness
22Cinni
33Auzzie Jay
Each owner is assigned a unique id (the ID field), but the numbers in website_id correspond to the ID in our websites table.

Table: website_descriptions

idwebsite_iddescription
11a personal site about the internet
22a cool website by Auzzie Jay
33a nostalgia-inspired website
Again, the ‘ID’ field here is unique to each item in this table, but website_id corresponds with each website.

Since the Owners and Descriptions table have website_id columns that share the same data, we have everything we need to start joining!

Writing your JOIN query

A JOIN is basically a fancy SELECT query. I like to fill in the column names that I’d like to select last, after filling out the rest of the query. So I might start with…

SELECT ... FROM website_urls JOIN website_owners ON website_urls.id = website_owners.id
Code language: SQL (Structured Query Language) (sql)

The … area is where I’m going to come back to once I’ve joined all of the tables.

After your SELECT … FROM, you’ll need a JOIN clause which shows the table name of the extra table you’re joining. The ON clause is what tells it how to join. In order to JOIN, it must share a column of data with another table, so the ON clause is doing just this.

You can chain together multiple JOIN clauses to join additional tables. I’m going to edit the query above to add on a second JOIN clause for the descriptions table.

SELECT ... FROM website_urls JOIN website_owners ON website_urls.id = website_owners.website_id JOIN website_descriptions ON website_urls.id = website_descriptions.website_id
Code language: SQL (Structured Query Language) (sql)

Finally, we should clarify which columns we want to join. This example is pretty simple, so we’re going to combine all of the unique ones. When specifying column names of tables we are joining, we have to prefix the column name with the table name and a period, like we use in our ON clause. This is not necessary for the table columns in the FROM clause.

I’ll modify the query to reflect that:

SELECT id, website_owners.name, website_descriptions.description FROM website_urls JOIN website_owners ON website_urls.id = website_owners.website_id JOIN website_descriptions ON website_urls.id = website_descriptions.website_id
Code language: SQL (Structured Query Language) (sql)

If you have a WHERE or an ORDER BY clause, you’ll want to include at the very end, after all of your JOINs.

Testing your JOIN query

When I create a new JOIN query, I like to use phpMyAdmin to test my query first, since PHP isn’t the greatest for seeing SQL errors. If you have this available, I highly recommend it.

  • Open phpMyAdmin and select your database.
  • Click on the tab that says SQL
  • Type or paste your query in the box and click Go.
  • It should take you to a view of your new JOIN table. If it does not, it will usually give you an error message to troubleshoot with.

Using JOINs in practice

You can write a JOIN query just like any other, using PHP. The only difference is, unless you explicitly display the data, it’s hard to visualize the table without seeing it, which is why I recommend testing it.

When grabbing the column names from within a query loop, you do not need to use the special prefix on the column names.



Hey, thanks for reading! If you have any questions, you can leave me a comment and I always try to reply.

If you have any suggestions on how I can improve this article, please let me know!

This website is not monetized in any way. It purely exists to help others on their web-building journey. However, if you have the means to donate toward my server costs, I have a ko-fi account where I accept tips.

Leave a Comment