Writing MySQL queries

Formatting

When writing queries for production (for use in code, or in reports) there are some steps you can take to make your queries easier to read.

Case

Query text that references MySQL elements (basic query syntax, functions, etc) should be in all uppercase letters, while references to elements of a database structure should be in lowercase. For example, instead of Template:MySQL you would write Template:MySQL

Backticks

When writing production queries, all references to database names, table names, column names, index names, etc should be enclosed in backticks.

For example, instead of Template:MySQL you would write Template:MySQL

Newlines

Add a newline before every major part of the query (FROM, JOINs, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT). i.e., instead of Template:MySQL you would use Template:MySQL

Referencing table names

MySQL allows you to reference column names without specifying the table name, as long as the column name is not ambiguous in your query. In other words, this: Template:MySQL is a valid query as long as `hat` is a column in either `thing1` or `thing2`, and not both. The same goes for the columns `something` and `somethingelse` - they can only be in one of the tables involved in the query.

However, the way you SHOULD write the query is like so: Template:MySQL

In large queries, it is MUCH easier to debug/comprehend queries that tell you the table names of all columns. Also, if a `hat` column is added to the `thing2` table at some point in the future, the former query will produce an error, while the latter will continue working.

Aliases

It is possible to give your tables aliases using the AS command, so that you can refer to them with an alternate name in a query.

If you are joining a table to itself, it is necessary to give the table an alias at least once.

However, in any other case, it is generally not a good idea in production code - when debugging large queries written by someone else, it's generally a lot easier to figure out where the 20 columns in a SELECT are coming from if the correct table name is used, so you don't have to figure out what "pl.name" is, or where "is.amount" might be in the database.

Common traps

Over the history of ITrack, there have been hundreds of bugs fixed in reports, as well as the software itself, that were the result of a logical error in a query.

By using what you know about the database structure, you should be able to write queries that you can guarantee will always return the correct number of results. Here are some tips:

Table joins

Many of the queries you write will involve 2 tables that have a 1-to-1 or 1-to-many relationship with each other.

This means that you have one table that holds a bunch of records, where each entry links to exactly 1 row in another table - for example, every record in an "inventory" table will have a relationship to a single "inventory type" record in another table.

You may know that inventory records have an inventory type, and you will probably have to write a query that joins them together at least a few times.

When that time comes, you may think to yourself "but how do I know which fields to use to join them together? What will I be joining on?"

Your first step should always be LOOK AT THE PRIMARY KEY. If you're joining the inventorytype table onto the inventory table, look at the primary key of the inventorytype table. That way, you will know what fields are the bare minimum that must be referenced in your query.

Grouping and summarizing information

Many queries you write will use GROUP BYs and aggregate functions (like SUM(), AVG(), and COUNT()) to gather information.

If you disobey the following rule, your results WILL BE WRONG. Maybe not on the first dataset you test it with, but it will be inevitable.

It's a very important rule. Don't fucking forget it.

Never use more than 1 many-to-many JOINs in the same query that uses aggregate functions

This query has a logical bug:

If any inventory record matches with more than one serialized record, or with more than one sales order lineitem, the SUMs will return unexpected values.