Why this sql can be written like this?-CodePudding

I want to group by ssex field and then take out the records with the largest sage within each group .


select * from student  ,
 
  (select max(sage) maxAge,ssex from student group by ssex) as st2 

where student.ssex=st2.ssex and student.sage= st2.maxAge;

Question: why can a subquery be used after "from student" ?

CodePudding user response：

This works because in the relational set theory which underpins all SQL databases, tables are formally defined as a persisted form of relation. Query results are just another form of relation. In queries, you can JOIN any kind of relation (table, subquery, view, table-value constructor, whatever) to any other kind of relation.

So the code we have in the question uses the old join syntax that has been obsolete since 1992 to join a table relation with a query relation. It should be written like this:

SELECT * 
FROM student st1
INNER JOIN (
        SELECT max(sage) maxAge, ssex 
        FROM student 
        GROUP BY ssex
    ) as st2 ON st1.ssex = st2.ssex and st1.sage = st2.maxAge;

Hopefully that makes more sense to you.

But also, today we would write this kind of query with a windowing function instead of a self-join anyway, which should perform better:

SELECT *
FROM (
    SELECT *, dense_rank() over (partition by ssex order by sage desc) dr
    FROM Student
) s
WHERE dr = 1

I would expect this to run in a small fraction of the time as the original.

So while we see the original code can be written that way, there are two good reasons it should not be written that way.

CodePudding user response：

select can specify multiple tables, and the cartesian product will be performed between these tables

SELECT fieldName FROM table1，table2 … WHERE table1.field = table2.fied AND other condition

table2 can be a subquery.