INNER JOIN ON LEFT JOIN. What best way solve problem?

If you need inner join on a left join what variant better for performance and coding-style?

"Cross" joins

SELECT 
demo.id AS demoId,
demo2.ID AS demo2Id,
demo3.ID AS demo3Id
FROM demo
LEFT JOIN (demo2
    INNER JOIN demo3
    ON demo3.ID = demo2.ID)
ON demo2.ID = demo.ID

or one more request?

SELECT 
demo.id AS demoId,
demo23.ID AS demo23Id
FROM demo
LEFT JOIN (
  Select demo2.ID
  FROM demo2 
    INNER JOIN demo3
    ON demo3.ID = demo2.ID) AS demo23
ON demo.ID = demo23.ID

Solution

They will be exactly the same in almost every modern DBMS.

The first query is essentially the same as the second one once optimization happens.

The compiler in most DBMS will construct a tree of logical steps to then optimize. For the first query, it will get something like this

SELECT
  |
 left  --> demo
  |
inner --> demo2
  |
  -->  demo3

For the second one, it might do something like

SELECT
  |
 left  --> demo
  |
 subquery --> inner --> demo2
                |
                -->  demo3

The subquery will then get optimized away to finish up with the same as the first version. In some DBMSs, the first version may start off like the second anyway, with a subquery.

Then further steps may transform it into right-joins or nested applys depending on statistics etc, so whichever way round you do it you will probably get the same thing.

As far as coding style, that's opinion-based. I personally prefer the first version, but others find it too confusing and prefer the second version.

Note that in many DBMSs the () in the first version is optional, as it's purely down to the order of the ON clauses. It's probably better to put them in because it's clearer.