Question: Refer to the below SQL statement for syntax highlighted in bold.
FROM provider_payments t1
WHERE t1.id = (SELECT MAX(t2.id)FROM provider_payments t2 WHERE t2.account_id = t1.account_id)
Normally after the FROM clause, we would see “tablename1, tablename2”, but here we can not see a comma behind the table name. Read on below explanation to understand what does SQL aliases syntax do and mean.
Explanation #1: The WHERE clause is built up out of a subquery. The highest ID from provider_payments table is selected which fits the accound_id. As both queries reflect the provider_payments table there may be several lines for one account_id and the query takes the one which the highest ID.
Explanation #2: t1 is the table alias for the provider_payments table. Table alias it’s a way to create a shortcut when having to add the table name to columns like t1.id
Otherwise, you would have to repeat the long table name: provider_payments.id
It is also written as:
FROM provider_payments as t1
In some SQL’s, the ‘as’ is optional. Refer to W3Schools > SQL > SQL Aliases for more information.