Example of mysql Subquery where/having/in/exists/from

Where/having query

MySQL can include subqueries in the where clause or having clause. Subqueries included in parentheses have a higher priority than comparison and logical operators, in operators, or exists operators.

You can also use a subquery before a comparison operator in a having clause to filter the groups created by the parent query.

Differences between where and having

HAVING is to GROUP and filter records first, and WHERE filters records before aggregation. That is to say, it works before the group by clause and HAVING clause. HAVING clause filters GROUP records after aggregation.

Different objects. The WHERE clause acts on tables and views, and the HAVING clause acts on groups. WHERE selects the input row before grouping and clustering calculation (therefore, it controls which rows enter clustering calculation), while HAVING selects the row of the group after grouping and clustering. Therefore, the WHERE clause cannot contain clustering functions, because it is meaningless to try to use clustering functions to judge the rows input to clustering operations. Conversely, HAVING clauses always contain aggregate functions. (Strictly speaking, you can write a HAVING clause that does not use clustering, but it is only effort-consuming. The same condition can be used more effectively in the WHERE stage .) For example:

Select * from tablename where id> 1; and

Select * from tablename having id> 1

There is no difference between the two query results. Having is not recommended.

There are two words to summarize:

The WHERE statement is prior to the GROUPBY statement, and the SQL statement is calculated before the group.
HAVING statement after the GROUPBY statement; SQL calculates the HAVING statement after the group.

In/exists/from query

The comparison operator is applicable only when the result column returned by a subquery contains a value. The result set returned by a subquery is a list of values. in this case, the comparison operator must be replaced by the in operator.

The in operator can detect whether a specific value exists in the result set. If the detection succeeds, an external query is executed.

The exists operator can be used to query whether a subquery produces results. This allows an external query to be executed only when the exists detection returns true.

You can also use the results of the subquery as a table in the from clause of a select statement.

Therefore, the results table generated by the internal query is used in the from clause of the external query. Such a table is converted into an export table in SQL.

Note that when using the subquery results in this way, the results table generated by the internal query must first have another table name. Otherwise, MySQL does not know how to refer to the columns.

In example

Select id, title from article where id in (
428,429,430,431,432
)

Exists example

SELECT c. CustomerId, CompanyName

From mers c

Where exists (

SELECT OrderID FROM Orders o

WHERE o. CustomerID = cu. CustomerID)

From Example

Select id, title from article where id in (
Select article_id from (select article_id from article_tag where tag_id = 135) as tbt
)