CODEWITHSUNDEEP
mysql
Xez
Xez

Reputation: 57

Is it correct to write a MySQL query like this?

Hello everyone what i want to know is if writing a query like this is correct:

SELECT (SELECT t.name FROM type t WHERE t.id=a.id_type) AS TYPE,
       a.title title,a.description description,u.name name,u.email email
FROM advert a 
INNER JOIN user u ON u.id=a.id_user  
WHERE a.id='2';

I tested the query and it works, but what i want to know if it is correct by the standards to use a subquery and then a join . Sorry if my question is stupid but i could't find an answer.

Upvotes: 1

Views: 50

Answers (2)

Barmar
Barmar

Reputation: 781592

Yes, this is allowed. It's called a correlated subquery. However, I think MySQL usually executes joins better:

SELECT t.name AS type, a.title title,a.description description,u.name name,u.email email
FROM advert a
INNER JOIN user u ON u.id=a.id_user
LEFT JOIN type t ON t.id = a.id_type
WHERE a.id = '2'

Upvotes: 2

valex
valex

Reputation: 24144

It's correct but I think better to use a JOIN instead of a subquery. Also if you use a subquery you should to make sure it returns only ONE row.

SELECT t.name AS TYPE,
       a.title title,a.description description,u.name name,u.email email
FROM advert a 
INNER JOIN user u ON u.id=a.id_user  
LEFT JOIN type t on t.id=a.id_type
WHERE a.id='2';

Upvotes: 0

Related Questions