what's the key word as in sql really indicate

Question

1:here is a SQL query , which i find in the book

 select distinct T.branch_name
        from branch as T, branch as S
        where T.assets > S.assets and
        S.branch_city = 'Brooklyn'

what i am confused is T and S both indicate table branch right? why they use T.assets>S.assets, is it the same as branch.asset > branch.asset what's the difference.

2: the meaning of the following query is to "Find all customers who have an account at all branches located in Brooklyn" question2

But I did not get it somehow, also struggle with the as key word

The query takes the same table twice, and make a join between them. T and S refers to the same table, but the table is copied twice into memory to resolve the query, so T and S refer to the 2 different copies in memory. — Jazzwave06, Oct 23 '14 at 02:18
Your book is dated. Avoid using non-ansi standard join syntax. Instead of `branch as T, branch as S where T.assets > S.assets` write it as `branch AS T INNER JOIN branch AS S ON T.assets > S.assets AND S.branch_city = 'Brooklyn'` — billinkc, Oct 23 '14 at 03:04
@sturcotte06: it's probably not implemented as a copy as such, but it can logically be treated as two tables that have the exact same data as if by a copy. — siride, Oct 23 '14 at 03:44

score 2 · Accepted Answer · answered Oct 23 '14 at 02:30

Think of code like this:

for (i=0; i<10; ++i)
    for (j=0; j<10; ++j)
        print i, j

How can i and j not be the same value? Easy -- they are both looping over the same set of values from 0 to 9, but on each iteration of the loop, they each have a different value within that range of integers.

That's similar to table aliases. Think of a join like this kind of nested loop. Each time two rows are joined, S and T are correlation names (which is the technical term) that reference one row at a time in your branch table.

what's the key word as in sql really indicate

1 Answers1