Mysql subquery result in "where" clause

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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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:

Method 3

While this is actually valid SQL:

select (select A from B where A = D.A ) as C 
from D

You are much better-off (performance-wise) implementing a JOIN instead:

SELECT D.A
FROM D
INNER JOIN B ON B.A = D.A

Method 4

No not as illustrated; but you could run the query in both places. or create a temp table with those results and join them in making it available to the query..

Select C from D inner join (Select A from B where...) C on C.1=D.1 where C like....

Method 5

If you mean comparing the results of a subquery in a where, Yes you can do this.

select X ... where (select Y ... ) = Z

However, probably a bad idea. Generally when you have to do this, it’s best to look for a way to streamline it into the main select. There are very creative ways to avoid nested queries. The reason being is that every selection has to do the inner query every time

If you mean performing a where against a subquery, you just select all and apply where. However, again, you could just apply another where.

select X ... where Y = Z and A = B.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating