Ok, this isn't LLBL Gen related (although discussion of this did come up in a thread here in the past about LLBL-generated SQL).
I was writing a demo case for a small best practices document at work, and I was planning on using test cases like these (using Northwind):
SELECT
o.orderid,
o.orderdate,
(SELECT companyname FROM shippers s WHERE s.shipperid = o.shipvia) companyname,
(SELECT lastname + ', ' + firstname FROM employees e WHERE e.employeeid = o.employeeid) FullName
FROM
orders o
WHERE
o.orderdate between '1-1-1996' and '12-31-1996'
ORDER BY
o.orderdate
SELECT
o.orderid,
o.orderdate,
s.companyname,
e.lastname + ', ' + e.firstname FullName
FROM
orders o
INNER JOIN shippers s ON s.shipperid = o.shipvia
INNER JOIN employees e ON e.employeeid = o.employeeid
WHERE
o.orderdate between '1-1-1996' and '12-31-1996'
ORDER BY
o.orderdate
The first uses subqueries, the second uses joins.
The point was going to be: don't use a subquery when you can use a join, as it's less efficient.
If you run these queries as-is, the execution plan shows a (slightly) better "cost" efficiency for the one that uses joins.
However, if you remove the WHERE and ORDER BY clauses, the subquery has a (significantly!) better cost efficiency (running them together in QA and then checking the combined execution plan is the easiest way to get an idea of relative costs, if you want to try this yourself).
Anyone know why the subqueries would be more efficient in this case?
If the tables had 50,000 records in them, do you think the joins would be more efficient?