MySQL – Uncommon Solutions

INNER JOINS with LEFT JOINS

In this scenario, I was attempting to Left Join a table to another table and Inner Joining them to a third table. I ended up getting no results from the query.

A bit of research found this link:

http://www.sitepoint.com/forums/showthread.php?769412-Combining-Left-and-Inner-Joins

In short, you have to perform the Inner Joins first before you Left Join.

My solution was to Left Join the entire way. My reason for the Inner Join in the first place, and the reason why I’m unsure of this method was that it seemed like a good way to kill performance. However, I’m not at the stage where I need to kill time performance tuning the system so I’ll have to ignore it for now.

I’m probably wrong anyway, but it wouldn’t hurt to check when I finally get the time.

Differences between ON AND VS WHERE in SQL for Outer Joins

When joining, I like to use ON + AND a lot. I heard that this tends to have better performance than running a WHERE query. And it worked well as long as I stayed with INNER JOINS. Imagine my surprise when I needed to do a LEFT JOIN while performing a conditional match. I expected a single row back, but SQL returned every single possible row instead.

After searching around, I found this link:

http://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh

Currently, I’m building up a website, so I can’t begin using Explain to tune. In the meantime, I’m going to use WHERE to filter the list whenever I have an Outer join. If this ends up killing performance, I’ll have to find a way to alter the queries later.

Aliases in a Select Statement

Did you know that if you define an alias when selecting columns, you probably can’t use it inside of a WHERE clause without getting an Unknown Column error?

The standard explanation given by This site (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html) and used by many of the sites that I found with google explains it by saying that:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

I think I found a layman’s explanation for the problem here:
http://stackoverflow.com/questions/153598/unknown-column-in-where-clause

SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name as not yet occurred.

And I believe a workable solution can be found here:
http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error

So…instead of using WHERE, use HAVING.

Here’s the conclusion of when to use either as found by:
http://www.codeproject.com/Articles/25258/Where-Vs-Having-Difference-between-having-and-Wher

Here’s the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

and

Here’s another rule: You can’t use HAVING unless you also use GROUP BY.

Sql Patterns

http://stackoverflow.com/questions/1382842/mysql-insert-joins

INSERT INTO tableNew (col1, col2)
  SELECT tbl1.col1, tbl2.col2
  FROM tbl1 JOIN tbl2

DELETE users, comments
FROM users JOIN comments ON comments.author=users.id
WHERE users.isspammer=1

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active',
A.status='Active'
Where A.id=1 or A.id=2 or A.id=3

Coalesce

Here’s an interesting SQL function:

Coalesce

Given a list, returns the first non-null value in the list. If all values are null, returns null.

http://www.creativedev.in/2012/03/mysql-coalesce-function/
http://stackoverflow.com/questions/6862373/mysql-join-with-insert-combined

Arguments against exposing auto-incrementing numbers for SQL queries

This link had some good arguments against exposing auto-incrementing numbers on a web page.

http://stackoverflow.com/questions/8078912/is-mysql-auto-increment-safe-to-use-as-userid

But generally, web developers feel uncomfortable exposing IDs that allow guessing other IDs by just incrementing or decrementing a number. Many resort to random, multi-digit IDs instead.

On a more obscure note, numeric IDs may also allow competitors to estimate your growth by keeping track of how the incremental value increases.

Find the Bug!

Here’s the query to troubleshoot:

UPDATE `Items` SET ( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 );

When you run the above query, you’ll get a funny message:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 )' at line 1

So where’s the syntax error?

It turns out that the parenthesis within the SET statement is a syntax error. I’m currently not sure why the parenthesis cause problems, but you need to take them out in order to run the above query.

So, the working query would be:

UPDATE `Items` SET `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') WHERE ( `Serial`=33 );

Starting and Ending Transactions

Following the spirit of atomic operations, I found a link on starting and ending a transaction.

http://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli

And this might be a good link for figuring out where to place the commit statement when using prepared statements.

http://stackoverflow.com/questions/10642635/how-can-i-use-prepared-statements-combined-with-transactions-with-php

Atomic operations and trying to report the right quantities.

A peeve of mine is trying to purchase something online from an e-commerce site which specifically states In Stock, and then waiting for weeks only to discover I’ve been back-ordered. It’s doubly bad when I’m trying to buy myself a christmas present. Yup, myself.

I haven’t tried the method listed in the below link yet, but I’m setting it aside in case I need it in the future.

http://stackoverflow.com/questions/11278494/mysql-atomic-operations-and-table-locking

Automatic Dates

It’s easier if you don’t have to use a query to insert the current time and date. This is particularly useful for “date created” and “date modified” columns. MySQL doesn’t let you do both at the same time for one table, but there are ways around it.

http://joegornick.com/2009/12/30/mysql-created-and-modified-date-fields/

I used the second option, inserting a null into the column. I read somewhere that it’s sort of hacky, however it appears to be legitimate behavior as seen in below link.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html (Scroll to the bottom)

Personally, I prefer to insert a NOW() value instead of NULL since it’s more intuitive.