MySQL Forums
Forum List  »  Newbie

No, this is not correct
Posted by: Владислав Сокол
Date: December 28, 2021 11:25AM

No, the query is incorrect.

If more than one id matches the same name in table2 then the query will fail with error "Subquery returns more than one row".

If id in table2 is unique (maybe primary key) then the query will not fail. But this does not mean that the query is correct in this case.

You may fix this problem by one of:

1) Replace "where ID = ( ..." with "where ID = ANY ( ..." or "where ID = SOME ( ..."

2) Add "ORDER BY ??? LIMIT 1" into subquery (replace "???" with some column name or expression which provides rows uniqueness in table2).

3) Rewrite the query to

SELECT [DISTINCT] table1.* FROM table1 JOIN table2 ON table1.id = table2.id WHERE table2.name = 'JOIN';

Еhe need to use DISTINCT depends on the structure of the tables.

Options: ReplyQuote


Subject
Written By
Posted
December 26, 2021 07:39PM
December 26, 2021 07:44PM
No, this is not correct
December 28, 2021 11:25AM
January 04, 2022 04:13AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.