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 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.

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.