How inefficient are virtual table JOINs?

Question

Say I have a query like this, where I join a number of virtual tables:

SELECT table1.a, tbl2.a, tbl3.b, tbl4.c, tbl5.a, tbl6.a
FROM table1
JOIN (SELECT x, a, b, c FROM table2 WHERE foo='bar') tbl2 ON table1.x = tbl2.x
JOIN (SELECT x, a, b, c FROM table3 WHERE foo='bar') tbl3 ON table1.x = tbl3.x
JOIN (SELECT x, a, b, c FROM table4 WHERE foo='bar') tbl4 ON table1.x = tbl2.x
JOIN (SELECT x, a, b, c FROM table5 WHERE foo='bar') tbl5 ON table1.x = tbl5.x
JOIN (SELECT x, a, b, c FROM table6 WHERE foo='bar') tbl6 ON table1.x = tbl6.x
WHERE anotherconstraint='value'

In my real query, each JOIN has its own JOINs, aggregate functions, and WHERE constraints.

How well/poorly would a query like this run? Also, what is the impact difference between this and running all of the individual virtual tables as their own query and linking the results together outside of SQL?

Answer 1

There's nothing inherently bad about using inline views (which is AFAIK the correct term for what you call "virtual tables"). I do recommend learning to view and understand execution plans so you can investigate specific performance issues.

In general, I think it's a very bad idea to execute multiple single-table queries and then essentially join the results together in your front-end code. Doing joins is what an RDBMS is designed for, why re-write it?