Menu
Is it possible to execute mysql query like this?
select (select A from B where ... ) as C from D where C like ' ... ' I need to use the result of subquery in general "where" clause.
636
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
Subqueries in the WHERE Clause. A subquery in a WHERE clause can be used to qualify a column against a set of rows. For example, the following subquery returns the department numbers for departments on the third floor. The outer query retrieves the names of employees who work on the third floor.
References to columns in the parent query are allowed only in the WHERE clause of the subquery. Subquery predicates that reference a column in a parent query must use the Equals To ( = ) predicate operator. Subquery predicates may not refer only to columns in the parent query.
A subquery cannot contain a BETWEEN or LIKE clause. A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.
You can wrap it in a sub-query like this:
SELECT * FROM ( select (select A from B where ... ) as C from D ) subq WHERE C like ' ... ' Have you read this?
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
A subquery is a SELECT statement within another statement.
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
Here is an example of a subquery:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested within the outer query, and in fact it is possible to nest subqueries within other subqueries, to a considerable depth. A subquery must always appear within parentheses.
The main advantages of subqueries are:
They allow queries that are structured so that it is possible to isolate each part of a statement.
They provide alternative ways to perform operations that would otherwise require complex joins and unions.
Many people find subqueries more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”
Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
