SQL Techniques

Basics

Concepts

• SQL constraints are enforced after each statement. Some databases support deferred constraints (which allow constraints to be enforced only on transaction boundaries).

Requests

• To update all rows of a given table:

UPDATE member SET theme_edition_support=1;

• To rename a table:

RENAME TABLE old_archive TO archive;

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);

Joins

• 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;

WHERE for Joins

• Be careful about putting logic about the joins into the WHERE clause, if you have multiple left joins for instance. Most of the time it's not what you want, because ifonly one condition fails, the whole line (row) will not be retained. Usually the criteria should be put within the ON clause.

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.

Useful functions and operators

• COALESCE() returns the first non null argument. It can be very useful with some queries, and is supported in Hibernate.
• CASE WHEN ... THEN ... ELSE ... END is a construct that can simulate some basic logic (switch, or if). For example you can do a:

SELECT * FROM WHERE id = CASE WHEN EXISTS(SOME QUERY) THEN 5 ELSE 10 END

Most of the times, I think this kind of query is better expressed in another way though.

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.