novel way to subselect?

[bryndyment]

Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.

SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";

Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

Just curious...

[raf]

You probably don't see it in any textbook because it simply doesn't make sense to mix the two syntax-forms.

It's not easy to read, and most recent code will look like

select var1, var2 from (table1 INNER JOIN table2 ON table1.PK = table2.FK) INNER JOIN table3 ON table2.var3 = table3.PK


And i don't see why that would be a subquery

It's not because subquerys sometimes can be replaced by joins (which almost always performs a lott better), that a three table join is a subquery. You often can rewrite them, but then you just have a plain join that might give you the same results (but faster).

[bryndyment]

Hi, thanks for the feedback... appreciated.

I'm not sure if the format you provided works in pre-4.1 versions of MySQL (when subqueries were introduced, according to my book). I'm stuck using 4.0.16 in my current project, so was looking for another way to "subquery". But maybe I'm using the term "subquery" incorrectly. I viewed it as performing an additional join/etc on the results of an earlier join operation, but all within a single SELECT statement.

But, I agree that the syntax you provide is much more elegant. My suggestion was simply for earlier versions of MySQL that don't (?) support that syntax.

[bryndyment]

"A subquery is a SELECT statement inside another statement." (MySQL docs).

My wrong!

Also, your syntax works great, and, I agree, is much easier to read. Thanks again...

[raf]

You're welcome.

Example of subquerys are

SELECT var1, var2 FROM table WHERE id In (SELECT max(id) FROM table2 WHERE foo='bar')

and that sort a things. But mySQL only has basic support for subquerys ('subselects') (which is one of the reasons why some people don't concider it to be a real db) and most hosts don't have version 4.1 isntalled so in development, you currently need to work your way around it ...
Subselects also don't perform well and i never timed it but i don't suppose they are much more performant then running two querys and use the results from the first one to build a condition for the second one.

If you're expecting high load on your app then you'd probably best run v 4.1 or switch to PostGreSQL which has full support for subquerys.