Having more than one INNER JOIN to the same table can be confusing.

More than one INNER JOIN can create a messy SQL statement.

If you don’t use aliases they can become difficult to read.

So join them with aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

If you want to find out who created the product, you can do that with a simple SQL query like this:

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table. Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t. In this case you need to use LEFT JOIN like this:

Unsure what alternatives there are to MySQL?

Looking for increases in MySQL performance?

You might not realize that there are some compatible relational database engines. That you can drop in to replace MySQL.

You can even run SQLite without needing a server stack.

SQLite is the most widely deployed database engine in the world

Each engine has it’s own pros and cons to consider before switching from one to another. Most are available as a drop in replacement for MySQL, but yet dropping out and back to MySQL may not be as smooth. Development of new versions means the code bases and features diverge from each other.

MariaDB, Percona Server and Amazon Aurora all offer performance improvements over vanilla MySQL. All for free:

Relational Database Engine

Details

Costs

Link

Amazon Aurora

Amazon Aurora is a MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one tenth the cost.

MySQL is the world’s most popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications.

Thinking of moving from regular MySQL to MariaDB?

Need any more reasons than performance to switch?

Switching from MySQL to MariaDB is easy, it’s still considered a drop in replacement.

MariaDB definitely has more of an open source attitude.

Galera implementation is better.

Maria comes by default with some distros, like the Red Hat series.

I recently found MariaDB 10 was available on my hosting environment. I saw that MariaDB was compatible with MySQL having not heard of it before then. I tried setting it up and switching was dead easy, just like MySQL and I didn’t have to make any code changes.

If you found the reasons above to consider switching to MariaDB helpful. Please comment and subscribe below so you don’t miss out on my next tips…

Having more than one join to the same table can be confusing.

More than one join can quickly create a messy SQL statement. If you don’t use aliases they can quickly become difficult to read.

If your database tables need to have three or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored. Finally a Users id is recorded to approve the changes.

Products table fields:

Comments

id

Primary Key

created_by_user_id

Foreign Key to Users table

modified_by_user_id

Foreign Key to Users table

approved_by_user_id

Foreign Key to Users table

Users table fields:

Comments

id

Primary Key

first_name

last_name

If you want to find out who created the product, you can do that with a simple SQL query like this:

But what if a product has only been created and modified but hasn’t yet been approved? In which case the approved_by_user_id should be null in your products table. Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t. In this case you need to use a LEFT JOIN like this: