SQL Techniques

A page listing some useful SQL techniques.

Sorting based on an array

Suppose you have an array of ids: (12,5,68,93). This array is exactly sorted in PHP as you want, and you need to retrieve these rows on the same order as in the array. You can do that with the FIELD function. This would give the following SQL query:

SELECT * from MyTable WHERE id IN (12,5,68,93) ORDER BY FIELD (id,12,5,68,93);

JOIN

• A LEFT JOIN specifies that even if no line is found on the corresponding joined table, a line will be created (containing the values of the first table, and blank values for the second table).

SELECT t2.id,  t1.description, t1.ID FROM Item AS t1 LEFT JOIN Category AS t2 ON t1.category_id = t2.id;

This would take all the descriptions from the table Item, and the id from Category if a matching line is found. If no matching line is found, id will be blank.

• A RIGHT JOIN does exactly the same thing, except the values from the joined table are present if there is no line found on the original table. The values from the original table are blank on this case.

SELECT t2.id,  t1.description, t1.ID FROM Item AS t1 RIGHT JOIN Category AS t2 ON t1.category_id = t2.id;

This would take all the ids from the table Category, and the description from Item if a matching line is found. If no matching line is found, description will be blank.

• An INNER JOIN removes all lines that don't match the condition.

SELECT t2.id,  t1.description, t1.ID FROM Item AS t1 INNER JOIN Category AS t2 ON t1.category_id = t2.id;

This would retrieve the id and the description, but only for the lines where t1.category_id = t2.id.

• Note that you can implicitly realize an INNER JOIN like this (in MySQL):

SELECT table1.id,  table1.description, table2.id FROM table1, table2 WHERE table1.id = table2.id;

AS

• The AS keyword in a JOIN is not mandatory at all, but is usually very convenient, since if you don't use it you have to specify always the full table name in the fields. Specifying the table name in a JOIN request is only requested if there is ambiguity, but it's good practice anyway.

Multiple SQL Queries

• You can send multiple SQL queries at once (separating each query with a semi-colon in the same string). However this is illegal in PHP because of security implications. But other languages may allow you to do that.