I have 3 tables and T1,T2,T3 and T1 has a million rows T2 have 300 rows and T3 has 400 Rows. Also I just need only 1 Column from T2 and T3 tables.

Now, which is the best performed SQL stmts (Response time and I/O)

Option 1)

SELECT t1.*,
t4.Desc,
t3.StatusDesc
FROM Product t1,
Language t2,
Lookup t3,
Description t4
WHERE t1.description_id = t4.ID AND
t1.Status_id = t3.ID AND
t2.ID = t4.Language_ID AND
t2.ID = t3.Language_ID AND
t2.ID = 'en'

Option 2)

SELECT t1.*,
(SELECT Desc
FROM Description t4,
WHERE t4.Language_ID = t2.ID AND
t1.description_id = t4.ID ) Desc,
(SELECT StatusDesc
FROM Lookup t3,
WHERE t4.Language_ID = t2.ID AND
t1.Status_id = t3.ID ) StatusDesc,
FROM Product t1, Language t2
WHERE t2.ID = 'en'





Option 3)

FUNCTION UDF_getDESC(id, language_id)
{
SELECT Desc
FROM Description t4,
WHERE t4.Language_ID = t2.ID AND
t1.description_id = t4.ID ;
return DESC;
}

FUNCTION UDF_getStatus(id, language_id)
{
SELECT StatusDesc
FROM Lookup t3,
WHERE t4.Language_ID = t2.ID AND
t1.Status_id = t3.ID ;

return StatusDesc;

}

Now,

SELECT t1.*,
UDF_getDESC(id, language_id) Desc,
UDF_getStatus(id, language_id) StatusDesc,
FROM Product t1, Language t2
WHERE t2.ID = 'en'

Did you run Explain on these SQLs to see which option is better choice?