MySQL/Language/Queries

SELECT
select syntax is as follows:

List of fields
You must specify what data you're going to retrieve in the SELECT clause: Any SQL expression is allowed here.

You can also retrieve all fields from a table: If you SELECT only the necessary fields, the query will be faster.

The table's name
If you are retrieving results from a table or a view, usually you specify the table's name in the FROM clause: Or: You can also use the `db_name`.`table_name` syntax: But you can also specify the table's name in the SELECT clause:

WHERE
You can set a filter to decide what records must be retrieved.

For example, you can retrieve only the record which has an id of 42: Or you can read more than one record:

GROUP BY
You can group all records by one or more fields. The record which have the same value for that field will be grouped in one computed record. You can only select the grouped record and the result of some aggregate functions, which will be computed on all records of each group.

For example, the following will group all records in the table `users` by the field `city`. For each group of users living in the same city, the maximum age, the minimum age and the average age will be returned: In the following example, the users are grouped by city and sex, so that we'll know the max, min and avg age of male/female users in each city:

HAVING
The HAVING clause declares a filter for the records which are computed by the GROUP BY clause. It's different from the WHERE clause, that operates before the GROUP BY. Here's what happens:


 * 1) The records which match to the WHERE clause are retrieved
 * 2) Those records are used to compute new records as defined in the GROUP BY clause
 * 3) The new records that match to the HAVING conditions are returned

This means which WHERE decides what record are used to compose the new computed records.

HAVING decides what computed records are returned, so it can operate on the results of aggregate functions. HAVING is not optimized and can't use indexes.

Incorrect use of HAVING: This probably gives a wrong results. MAX(age) and other aggregate calculations are made using all values, even if the record's sex value is 'f'. This is hardly the expected result.

Incorrect use of HAVING: This is correct and returns the expected results, but the execution of this query is not optimized. The WHERE clause can and should be used, because, so that MySQL doesn't computes records which are excluded later.

Correct use of HAVING: It must group all records, because can't decide the max age of each city before the GROUP BY clause is execute. Later, it returns only the record with a MAX(age)>80.

ORDER BY
You can set an arbitrary order for the records you retrieve. The order may be alphabetical or numeric. By default, the order is ASCENDING. You can also specify that the order must be DESCENDING: NULLs values are considered as minor than any other value.

You can also specify the field position, in place of the field name: SQL expressions are allowed: You can retrieve records in a random order: If a GROUP BY clause is specified, the results are ordered by the fields named in GROUP BY, unless an ORDER BY clause is present. You can even specify in the GROUP BY clause if the order must be ascending or descending: If you have a GROUP BY but you don't want the records to be ordered, you can use ORDER BY NULL:

LIMIT
You can specify the maximum of rows that you want to read: This statement returns a maximum of 10 rows. If there are less than 10 rows, it returns the number of rows found. The limit clause is usually used with ORDER BY.

You can get a given number of random records: You can specify how many rows should be skipped before starting to return the records found. The first record is 0, not one: You can use the LIMIT clause to get the pagination of results: Also, the following syntax is acceptable: You can use the LIMIT clause to check the syntax of a query without waiting for it to return the results: Optimization tips:


 * SQL_CALC_FOUND_ROWS may speed up a query
 * LIMIT is particularly useful for SELECTs which use ORDER BY, DISTINCT and GROUP BY, because their calculations don't have to involve all the rows.
 * If the query is resolved by the server copying internally the results into a temporary table, LIMIT helps MySQL to calculate how much memory is required by the table.

DISTINCT
The DISTINCT keyword can be used to remove all duplicate rows from the resultset: You can use it to get the list of all values contained in one field: Or you can use it to get the existing combinations of some values: If one of the fields you are SELECTing is the PRIMARY KEY or has a UNIQUE index, DISTINCT is useless. Also, it's useless to use DISTINCT in conjunction with the GROUP BY clause.

EXISTS and ALL
(Compatible: Mysql 4+)

Optimization hints
There are some hints you may want to give to the server to better optimize the SELECTs. If you give more than one hints, the order of the keywords is important:

HIGH_PRIORITY

Usually, DML commands (INSERT, DELETE, UPDATE) have higher priority than SELECTs. If you specify HIGH_PRIORITY though, the SELECT will have higher priority than DML statements.

STRAIGHT_JOIN Force MySQL to evaluate the tables of a JOIN in the same order they are named, from the leftmost.

SQL_SMALL_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return few rows.

SQL_BIG_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return a many rows.

SQL_BUFFER_RESULT Force MySQL to copy the result into a temporary table. This is useful to remove LOCKs as soon as possible.

SQL_CACHE Forces MySQL to copy the result into the query cache. Only works if the value of query_cache_type is DEMAND or 2.

SQL_NO_CACHE Tells MySQL not to cache the result. Useful if the query occurs very seldom or if the result often change.

SQL_CALC_FOUND_ROWS Useful if you are using the LIMIT clause. Tells the server to calculate how many rows would have been returned if there were no LIMIT. You can retrieve that number with another query:

Index hints

 * : specifies to research some records preferably by browsing the tables indexes.
 * : idem in more restrictive. A table will be browsed without index only if the optimizer doesn't have the choice.
 * : request to not favor the indexes.

Example:

UNION and UNION All
(Compatible: Mysql 4+)

Following query will return all the records from both tables.

UNION is the same as UNION DISTINCT.

If you type only UNION, then it is considered that you are asking for distinct records. If you want all records, you have to use UNION ALL.

Joins


The Most important aspect of SQL is its relational features. You can query, compare and calculate two different tables having entirely different structure. Joins and subselects are the two methods to join tables. Both methods of joining tables should give the same results. The natural join is faster on most SQL platforms.

In the following example a student is trying to learn what the numbers are called in Hindi.

Inner Join
You can also write the same query as

Remark: in MySQL,  is equivalent to   and   (Cartesian join).

A Cartesian join is when you join every row of one table to every row of another table.

It is also called Cross Join and may be written in this way:

Natural Join
Natural Joins give the same result as an  on all the two tables common columns.

The following statement using "USING" method will display the same results as the previous  (compatible: MySQL 4+; but changed in MySQL 5).

Left Join / Left Outer Join
The syntax is as follows: English tag   Hindi One     1     --no row-

Right Outer Join
English  tag     Hindi --no row--  4      Char


 * Make sure that you have the same name and same data type in both tables.
 * The keywords LEFT and RIGHT are not absolute, they only operate within the context of the given statement: we can reverse the order of the tables and reverse the keywords, and the result would be the same.
 * If the type of join is not specified as inner or outer then it will be executed as an INNER JOIN.

Full Outer Join
As for v5.1, MySQL does not provide FULL OUTER JOIN. You may emulate it this way:

Multiple joins
It is possible to join more than just two tables: Here is an example from Savane:

Subqueries
(Compatible: MySQL 4.1 and later)


 * SQL subqueries let you use the results of one query as part of another query.
 * Subqueries are often natural ways of writing a statement.
 * Let you break a query into pieces and assemble it.
 * Allow some queries that otherwise can't be constructed. Without using a subquery, you have to do it in two steps.
 * Subqueries always appear as part of the WHERE (or HAVING) clause.
 * Only one field can be in the subquery SELECT. It means Subquery can only produce a single column of data as its result.
 * ORDER BY is not allowed; it would not make sense.
 * Usually refer to name of a main table column in the subquery.
 * This defines the current row of the main table for which the subquery is being run. This is called an outer reference.

For e.g. If RepOffice= OfficeNbr from Offices table, list the offices where the sales quota for the office exceeds the sum of individual salespersons' quotas ??? is the sum of the quotas of the salespeople, i.e. We combine these to get Display all customers with orders or credit limits > $50,000. Use the DISTINCT word to list the customer just once.

Resources

 * Official MySQL documentation

MySQL/Requêtes