Syntax

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

tbl_index_list:
  tbl_name
    [[INDEX|KEY] (index_name[, index_name] ...)]
    [IGNORE LEAVES]

Description

The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit statement, or into the default key cache otherwise.LOAD INDEX INTO CACHE is used only for or tables.

The IGNORE LEAVES modifier causes only blocks for the nonleaf nodes of the index to be preloaded.

_{This page is licensed: GPLv2, originally from}

InnoDB supports row-level locking. Selected rows can be locked using LOCK IN SHARE MODE or . In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.

When LOCK IN SHARE MODE is specified in a statement, MariaDB will wait until all transactions that have modified the rows are committed. Then, a write lock is acquired. All transactions can read the rows, but if they want to modify them, they have to wait until your transaction is committed.

If is set to 1 (the default), the LOCK IN SHARE MODE and clauses have no effect in InnoDB. For non-transactional storage engines like MyISAM and ARIA, a table level lock will be taken even if autocommit is set to 1.

Description

You can use DUAL instead of a table name in situations where no tables are referenced, such as the following statement:

DUAL is purely for the convenience of people who require that all SELECT statements should haveFROM and possibly other clauses. MariaDB ignores the clauses. MariaDB does not require

Default Values

If the SQL_MODE contains STRICT_TRANS_TABLES and you are inserting into a transactional table (like InnoDB), or if the SQL_MODE contains STRICT_ALL_TABLES, all NOT NULL columns which do not have a DEFAULT value (and are not AUTO_INCREMENT) must be explicitly referenced in INSERT statements. If not, an error like this is produced:

In all other cases, if a NOT NULL column without a DEFAULT value is not referenced, an empty value will be inserted (for example, 0 for INTEGER columns and '' for CHAR columns). See for examples.

If a NOT NULL column having a DEFAULT value is not referenced, NULL will be inserted.

If a NULL column having a DEFAULT value is not referenced, its default value will be inserted. It is also possible to explicitly assign the default value using the DEFAULT keyword or the function.

If the DEFAULT keyword is used but the column does not have a DEFAULT value, an error like this is produced:

Duplicate Values

By default, if you try to insert a duplicate row and there is a UNIQUE index, INSERT stops and an error like this is produced:

To handle duplicates you can use the clause, or the statement. Note that the IGNORE and DELAYED options are ignored when you use .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

The MyISAM storage engine supports concurrent inserts. This feature allows SELECT statements to be executed during INSERT operations, reducing contention.

Whether concurrent inserts can be used or not depends on the value of the concurrent_insert server system variable:

• NEVER (0) disables concurrent inserts.

• AUTO (1) allows concurrent inserts only when the target table has no free blocks (no data in the middle of the table has been deleted after the last ). This is the default.

• ALWAYS (2) always enables concurrent inserts, in which case new rows are added at the end of a table if the table is being used by another thread.

If the is used, and statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way, the log can be safely used to restore data.

Concurrent inserts are not used by replicas with the row-based (see ).

If an statement contains the clause, concurrent inserts cannot be used. is usually unneeded if concurrent inserts are enabled.

uses concurrent inserts if the CONCURRENT keyword is specified and is not NEVER. This makes the statement slower (even if no other sessions access the table) but reduces contention.

allows non-conflicting concurrent inserts if a READ LOCAL lock is used. Concurrent inserts are not allowed if the LOCAL keyword is omitted.

Notes

The decision to enable concurrent insert for a table is done when the table is opened. If you change the value of , it will only affect newly opened tables. If you want it to work for also for tables in use or cached, you should do after setting the variable.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

With INSERT ... SELECT, you can quickly insert many rows into a table from one or more other tables. For example:

tbl_name can also be specified in the form db_name.tbl_name (see ). This allows to copy rows between different databases.

If the new table has a primary key or UNIQUE indexes, you can use to handle duplicate key errors during the query. The newer values will not be inserted if an identical value already exists.

can be used instead of INSERT to prevent duplicates on UNIQUE indexes by deleting old values. In that case, ON DUPLICATE KEY UPDATE cannot be used.

INSERT ... SELECT works for tables which already exist. To create a table for a given resultset, you can use .

See Also

_{This page is licensed: GPLv2, originally from}

InnoDB supports row-level locking. Selected rows can be locked using LOCK IN SHARE MODE or FOR UPDATE. In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.

The FOR UPDATE clause of SELECT applies only when autocommit is set to 0 or the SELECT is enclosed in a transaction. A lock is acquired on the rows, and other transactions are prevented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED).

If autocommit is set to 1, the LOCK IN SHARE MODE and FOR UPDATE clauses have no effect in InnoDB. For non-transactional storage engines like MyISAM and ARIA, a table level lock will be taken even if autocommit is set to 1.

If the isolation level is set to SERIALIZABLE, all plain SELECT statements are converted to SELECT ... LOCK IN SHARE MODE.

Example

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Single-table syntax:

Multiple-table syntax:

Description

Syntax

Or:

Syntax

Or:

Use the GROUP BY clause in a SELECT statement to group rows together that have the same value in one or more column, or the same computed value using expressions with any functions and operators except grouping functions. When you use a GROUP BY clause, you will get a single result row for each group of rows that have the same value for the expression given in GROUP BY.

When grouping rows, grouping values are compared as if by the = operator. For string values, the = operator ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use.

You can use any of the grouping functions in your select expression. Their values will be calculated based on all the rows that have been grouped together for each result row. If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY is used.

You can use multiple expressions in the GROUP BY clause, separated by commas. Rows are grouped together if they match on each of the expressions.

You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the _n_th column in the select expression.

The WHERE clause is applied before the GROUP BY clause. It filters non-aggregated rows before the rows are grouped together. To filter grouped rows based on aggregate values, use the HAVING clause. The HAVING clause takes any expression and evaluates it as a boolean, just like the WHERE clause. You can use grouping functions in the HAVING clause. As with the select expression, if you reference non-grouped columns in the HAVING clause, the behavior is undefined.

By default, if a GROUP BY clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY. You can also specify ASC or DESC (ascending, descending) after those expressions, like in . The default is ASC.

If you want the rows to be sorted by another field, you can add an explicit . If you don't want the result to be ordered, you can add .

WITH ROLLUP

The WITH ROLLUP modifier adds extra rows to the result set that represent super-aggregate summaries. For a full description with examples, see .

GROUP BY Examples

Consider the following table that records how many times each user has played and won a game:

Get a list of win counts along with a count:

The GROUP BY expression can be a computed value, and can refer back to an identifier specified with AS. Get a list of win averages along with a count:

You can use any in the select expression. For each win average as above, get a list of the average play count taken to get that average:

You can filter on aggregate information using the HAVING clause. The HAVING clause is applied after GROUP BY and allows you to filter on aggregate data that is not available to the WHERE clause. Restrict the above example to results that involve an average number of plays over 20:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

The InnoDB storage engine uses row-level locking to ensure data integrity. However some storage engines (such as MEMORY, MyISAM, Aria and MERGE) lock the whole table to prevent conflicts. These storage engines use two separate queues to remember pending statements; one is for SELECTs and the other one is for write statements (INSERT, DELETE, UPDATE). By default, the latter has a higher priority.

To give write operations a lower priority, the low_priority_updates server system variable can be set to ON. The option is available on both the global and session levels, and it can be set at startup or via the SET statement.

When too many table locks have been set by write statements, some pending SELECTs are executed. The maximum number of write locks that can be acquired before this happens is determined by the max_write_lock_count server system variable, which is dynamic.

If write statements have a higher priority (default), the priority of individual write statements (INSERT, REPLACE, UPDATE, DELETE) can be changed via the LOW_PRIORITY attribute, and the priority of a SELECT statement can be raised via the HIGH_PRIORITY attribute. Also, supports a LOW_PRIORITY attribute for WRITE locks.

If read statements have a higher priority, the priority of an INSERT can be changed via the HIGH_PRIORITY attribute. However, the priority of other write statements cannot be raised individually.

The use of LOW_PRIORITY or HIGH_PRIORITY for an INSERT prevents from being used.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

The DELAYED option for the statement is a MariaDB/MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MariaDB for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

Note that INSERT DELAYED is slower than a normalINSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should useINSERT DELAYED only when you are really sure that you need it.

The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mariadbd forcibly (for example, with kill -9) or if mariadbd dies unexpectedly, any queued rows that have not been written to disk are lost.

The number of concurrent INSERT DELAYED threads is limited by the server system variables. If it is set to 0, INSERT DELAYED is disabled. The session value can be equal to the global value, or 0 to disable this statement for the current session. If this limit has been reached, the DELAYED clause will be silently ignore for subsequent statements (no error will be produced).

Limitations

There are some limitations on the use of DELAYED:

• INSERT DELAYED works only with , , , and tables. If you execute INSERT DELAYED with another storage engine, you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported for table 'tab_name'

• For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM.

• INSERT DELAYED

See Also

_{This page is licensed: GPLv2, originally from}

The PROCEDURE clause of SELECT passes the whole result set to a Procedure which will process it. These Procedures are not Stored Procedures, and can only be written in the C language, so it is necessary to recompile the server.

Currently, the only available procedure is ANALYSE, which examines the resultset and suggests the optimal datatypes for each column. It is defined in the sql/sql_analyse.cc file, and can be used as an example to create more Procedures.

This clause cannot be used in a view definition.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

The LOAD XML statement reads data from an XML file into a table. Thefile_name

MINUS is a synonym for when is set.

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Or:

Or:

Description

INSERT ... ON DUPLICATE KEY UPDATE (often called "upsert") is a MariaDB/MySQL extension to the statement that, if it finds a duplicate unique or , will instead perform an .

The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.

If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index.

If the table has an and the statement inserts or updates a row, the function returns its AUTO_INCREMENT value.

The function can only be used in a ON DUPLICATE KEY UPDATE clause and has no meaning in any other context. It returns the column values from the INSERT portion of the statement. This function is particularly useful for multi-rows inserts.

The and options are ignored when you use ON DUPLICATE KEY UPDATE.

See for details on the PARTITION clause.

This statement activates INSERT and UPDATE triggers. See for details.

See also a similar statement, .

Examples

If there is no existing key, the statement runs as a regular INSERT:

A regular INSERT with a primary key value of 1 will fail, due to the existing key:

However, we can use an INSERT ON DUPLICATE KEY UPDATE instead:

Note that there are two rows reported as affected, but this refers only to the UPDATE.

Adding a second unique column:

Where two rows match the unique keys match, only the first is updated. This can be unsafe and is not recommended unless you are certain what you are doing.

Although the third row with an id of 3 has an id2 of 13, which also matched, it was not updated.

Changing id to an auto_increment field. If a new row is added, the auto_increment is moved forward. If the row is updated, it remains the same.

Refering to column values from the INSERT portion of the statement:

See the function for more.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Or:

Or:

Description

REPLACE works exactly like , except that if an old row in the table has the same value as a new row for a PRIMARY KEY or aUNIQUE index, the old row is deleted before the new row is inserted. If the table has more than one UNIQUE keys, it is possible that the new row conflicts with more than one row. In this case, all conflicting rows will be deleted.

The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see ). This allows to use to copy rows between different databases.

Basically it works like this:

The above can be replaced with:

REPLACE is a MariaDB/MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For other MariaDB/MySQL extensions to standard SQL --- that also handle duplicate values --- see and .

Note that unless the table has a PRIMARY KEY orUNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Values for all columns are taken from the values. See for details. Specified in theREPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as 'SET col = col + 1', the reference to the column name on the right hand side is treated asDEFAULT(col), so the assignment is equivalent to'SET col = DEFAULT(col) + 1'.

To use REPLACE, you must have both theINSERT and DELETE for the table.

There are some issues you should be aware of, before using REPLACE:

• If there is an field, a new value will be generated.

• If there are foreign keys, ON DELETE action will be activated by REPLACE.

• on DELETE and INSERT will be activated by REPLACE

To avoid some of these behaviors, you can use INSERT ... ON DUPLICATE KEY UPDATE.

This statement activates INSERT and DELETE triggers. See for details.

PARTITION

See for details.

REPLACE RETURNING

REPLACE ... RETURNING returns a resultset of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.

Examples

Simple REPLACE statement:

Using stored functions in RETURNING:

Subqueries in the statement:

Subqueries in the RETURNING clause that return more than one row or column cannot be used..

Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT.

REPLACE ... RETURNING returns a result set of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.

Examples

Simple REPLACE statement:

Using stored functions in RETURNING:

Subqueries in the statement:

Subqueries in the RETURNING clause that return more than one row or column cannot be used..

Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table. SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table.

See Also

• for details on the DELAYED clause

_{This page is licensed: GPLv2, originally from}

A query to retrieve the list of phone numbers for clients who ordered in the last two weeks might be written in a couple of ways. Here are two:

SELECT *
FROM
  clients
  INNER JOIN orders ON clients.id = orders.clientId
  INNER JOIN phoneNumbers ON clients.id = phoneNumbers.clientId
WHERE
  orderPlaced >= NOW() - INTERVAL 2 WEEK;

Does it make a difference? Not much as written. But you should use the second form, for these reasons:

• Readability. Once the WHERE clause contains more than two conditions, it becomes tedious to pick out the difference between business logic (only dates in the last two weeks) and relational logic (which fields relate clients to orders). Using the JOIN syntax with an ON clause makes the WHERE list shorter, and makes it very easy to see how tables relate to each other.

• Flexibility. Let's say we need to see all clients even if they don't have a phone number in the system. With the second version, it's easy; just change INNER JOIN phoneNumbers to LEFT JOIN phoneNumbers. Try that with the first version, and recent MySQL versions will issue a syntax error because of the change in precedence between the comma operator and the JOIN keyword. The solution is to rearrange the FROM clause or add parentheses to override the precedence, and that quickly becomes frustrating.

• Portability. If your queries use standard SQL syntax, you will have an easier time switching to a different database should the need ever arise.

See Also

• — A blog entry about this topic.

The initial version of this article was copied, with permission, from on 2012-10-05.

_{This page is licensed: CC BY-SA / Gnu FDL}

A scalar subquery is a subquery that returns a single value. This is the simplest form of a subquery, and can be used in most places a literal or single column value is valid.

The data type, length and character set and collation are all taken from the result returned by the subquery. The result of a subquery can always be NULL, that is, no result returned. Even if the original value is defined as NOT NULL, this is disregarded.

A subquery cannot be used where only a literal is expected, for example LOAD DATA INFILE expects a literal string containing the file name, and LIMIT requires a literal integer.

Examples

CREATE TABLE sq1 (num TINYINT);

CREATE TABLE sq2 (num TINYINT);

INSERT INTO sq1 VALUES (1);

INSERT INTO sq2 VALUES (10* (SELECT num FROM sq1));

SELECT * FROM sq2;
+------+
| num  |
+------+
|   10 |
+------+

Inserting a second row means the subquery is no longer a scalar, and this particular query is not valid:

No rows in the subquery, so the scalar is NULL:

A more traditional scalar subquery, as part of a WHERE clause:

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Single-table syntax:

Multiple-table syntax:

Or:

Trimming history:

Description

Use the LIMIT clause to restrict the number of returned rows. When you use a single integer n with LIMIT, the first n rows will be returned. Use the clause to control which rows come first. You can also select a number of rows after an offset using either of the following:

When you provide an offset m with a limit n, the first m rows will be ignored, and the following

You can control the ordering of execution on table operations using parentheses.

Syntax

Description

A row subquery is a returning a single row, as opposed to a , which returns a single column from a row, or a literal.

Examples

Finding all rows in one table and also in another:

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

SELECT ... INTO DUMPFILE

Although are more commonly placed in a WHERE clause, they can also form part of the FROM clause. Such subqueries are commonly called derived tables.

If a subquery is used in this way, you must also use an AS clause to name the result of the subquery.

ORACLE mode

MariaDB starting with

Syntax

Description

using the EXISTS

using the ALL keyword will return true if the comparison returns true for each row returned by the subquery, or the subquery returns no rows.

Syntax

Syntax

Or:

Or:

The INSERT statement is used to insert new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in the article.

The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see ). This allows to use to copy rows between different databases.

The PARTITION clause can be used in both the INSERT and the SELECT part. See for details.

The columns list is optional. It specifies which values are explicitly inserted, and in which order. If this clause is not specified, all values must be explicitly specified, in the same order they are listed in the table definition.

The list of value follow the VALUES or VALUE keyword (which are interchangeable, regardless how much values you want to insert), and is wrapped by parenthesis. The values must be listed in the same order as the columns list. It is possible to specify more than one list to insert more than one rows with a single statement. If many rows are inserted, this is a speed optimization.

For one-row statements, the SET clause may be more simple, because you don't need to remember the columns order. All values are specified in the form col = expr.

Values can also be specified in the form of a SQL expression or subquery. However, the subquery cannot access the same table that is named in the INTO clause.

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. If you use the HIGH_PRIORITY keyword, the statement has the same priority as SELECTs. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). However, if one of these keywords is specified, cannot be used. See for details.

INSERT DELAYED

For more details on the DELAYED option, see .

HIGH PRIORITY and LOW PRIORITY

See .

Defaults and Duplicate Values

See for details..

INSERT IGNORE

See .

INSERT ON DUPLICATE KEY UPDATE

See .

Examples

Specifying the column names:

Inserting more than 1 row at a time:

Using the SET clause:

SELECTing from another table:

See and for further examples.

INSERT ... RETURNING

INSERT ... RETURNING returns a result set of the inserted rows.

It returns the listed columns for all the rows that are inserted, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.

Examples

Simple INSERT statement:

Using stored functions in RETURNING

Subqueries in the RETURNING clause that return more than one row or column cannot be used.

Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values, and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used or it can be used in INSERT...SELECT...RETURNING if the table in the RETURNING clause is not the same as the INSERT table.

See Also

• Equivalent to DELETE + INSERT of conflicting row.

_{This page is licensed: GPLv2, originally from}

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs: Non-Recursive, which this article covers; and Recursive.

Non-Recursive CTEs

The WITH keyword signifies a CTE. It is given a name, followed by a body (the main query).

CTEs are similar to derived tables:

WITH engineers AS 
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' )

SELECT * FROM engineers
WHERE ...

A non-recursive CTE is basically a query-local VIEW. There are several advantages and caveats to them. The syntax is more readable than a nested FROM (SELECT ...). A CTE can refer to another and it can be referenced from multiple places.

A CTE referencing Another CTE

Using this format makes for a more readable SQL than a nested FROM(SELECT ...) clause:

Multiple Uses of a CTE

This can be an 'anti-self join', for example:

Or, for year-over-year comparisons, for example:

Another use is to compare individuals against their group. Below is an example of how this might be executed:

_{This page is licensed: CC BY-SA / Gnu FDL}

Subqueries using the ANY keyword will return true if the comparison returns true for at least one row returned by the subquery.

Syntax

The required syntax for an ANY or SOME quantified comparison is:

Or:

• scalar_expression may be any expression that evaluates to a single value.

• comparison_operator may be any one of =, >, <, >=, <=, <> or !=

ANY returns:

• TRUE if the comparison operator returns TRUE for at least one row returned by the Table subquery.

• FALSE if the comparison operator returns FALSE for all rows returned by the Table subquery, or Table subquery has zero rows.

• NULL if the comparison operator returns

SOME is a synonym for ANY, and IN is a synonym for = ANY .

Examples

100 is greater than two of the three values, and so the expression evaluates as true.

SOME is a synonym for ANY:

IN is a synonym for = ANY, and here there are no matches, so no results are returned:

Reading this query, the results may be counter-intuitive. It may seem to read as SELECT * FROM sq1 WHERE num does not match any results in sq2. Since it does match 100, it could seem that the results are incorrect. However, the query returns a result if the match does not match any of sq2. Since 100 already does not match 40, the expression evaluates to true immediately, regardless of the 100's matching. It may be more easily readable to use SOME in a case such as this:

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

LOAD DATA INFILE

A can quite often, but not in all cases, be rewritten as a .

Rewriting Subqueries as JOINS

A subquery using IN can be rewritten with the DISTINCT keyword. Consider this query:

It can be rewritten like this:

NOT IN

Syntax

Syntax

Description

SELECT INTO OUTFILE

There are a number of limitations regarding , which are discussed below.

The following tables and data will be used in the examples that follow:

ORDER BY and LIMIT

To use or limit in both must be used.. For example:

is valid, but

is not.

EXCEPT

The result of EXCEPT contains all records of the left SELECT result set except records which are in right SELECT result set. In other words, it is the subtraction of two result sets.

Description

Use the ORDER BY clause to order a resultset, such as that are returned from a statement. You can specify just a column or use any expression with functions. If you are using the GROUP BY clause, you can use grouping functions in ORDER BY. Ordering is done after grouping.

You can use multiple ordering expressions, separated by commas. Rows are sorted by the first expression, then by the second expression if they have the same value for the first, and so on.

Syntax

See .

Description

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. CTEs first appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.

There are two kinds of CTEs:

• ;

• Recursive, which this article covers.

SQL is generally poor at recursive structures.