Return to Answer

There is some terminology confusion

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. A subquery is technically query block within either WHERE or SELECT clause, e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case, inner view or subquery can be unnested into "flat" project-restrict-join. (Correlated subquery with aggregation unnests into inner views with grouping, which then unnests into flat query).

As for algebraic rules for query optimization, relational algebra is known to be axiomatized into Relational Lattice which simplifies query transformations as demonstrated here and there.

There is some terminology confusion; the query block within parenthesis

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. A subquery is query block within either WHERE or SELECT clause, e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case, inner view or subquery can be unnested into "flat" project-restrict-join. Correlated subquery with aggregation unnests into inner views with grouping, which then unnests into flat query.

select deptno from dept d
    where 3 < (select avg(sal) from emp e
               where d.deptno=e.deptno)

select d.deptno from dept d, ( 
    select deptno from emp e
    group by deptno
    having avg(sal) > 3
) where d.deptno=e.deptno

select d.deptno from dept d, emp e
where d.deptno=e.deptno 
group by d.deptno
having avg(sal) > 3

As for algebraic rules for query optimization, relational algebra is known to be axiomatized into Relational Lattice which simplifies query transformations as demonstrated here and there.

There is some terminology confusion

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. The subquery is technically query block within either WHERE or SELECT clause, e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case the query can be unnested into "flat" project-restrict-join.

As for algebraic rules for query optimization, relational algebra is known to be axiomatized into Relational Lattice which simplifies query transformations as demonstrated here and there.

There is some terminology confusion

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. A subquery is technically query block within either WHERE or SELECT clause, e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case, inner view or subquery can be unnested into "flat" project-restrict-join. (Correlated subquery with aggregation unnests into inner views with grouping, which then unnests into flat query).

As for algebraic rules for query optimization, relational algebra is known to be axiomatized into Relational Lattice which simplifies query transformations as demonstrated here and there.

There is some terminology confusion

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. The subquery is technically query block within either WHERE or SELECT clause (scalar subquery), e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case the query can be unnested into "flat" project-restrict-join.

As for algebraic rules for query optimization, relational algebra is known to be automatized into Relational Lattice which simplifies query transformations as demonstrated here and there.

There is some terminology confusion

SELECT t1.name, t2.address
  FROM table1 
  JOIN (SELECT id, address 
          FROM table2 AS t3 
         WHERE t3.id = t1.id) 

is called inner view. The subquery is technically query block within either WHERE or SELECT clause, e.g.

select deptno from dept
where 3 < (select count(1) from emp 
           where dept.deptno=emp.deptno)

In either case the query can be unnested into "flat" project-restrict-join.

As for algebraic rules for query optimization, relational algebra is known to be axiomatized into Relational Lattice which simplifies query transformations as demonstrated here and there.