What do the “l” and “p” mean in this SQL statement?

Question

I have the following statement which filters out a result from multiple tables. I am not quite understanding the p after tablename and the l after anothertablename. Are they generic or purposeful?

SELECT id, title, l.id, username FROM tablename p JOIN anothertablename l ON (l.id = p.id) WHERE p.id='15' LIMIT 1

Answer 1

Those are called alias

It's just an abbreviation to use instead of always using the full table names.

Imagine if you had two tables called Products_Orders_items and Customers_orders_items if both had the column id, in a JOIN you would write:

SELECT * 
FROM Products_Orders_items
INNER JOIN Customers_orders_items 
    ON Customers_orders_items.id = Products_Orders_items.id
WHERE Customers_orders_items.otherField = 'bla';

Using alias you can make it easier to read:

SELECT * 
FROM Products_Orders_items a
INNER JOIN Customers_orders_items b ON a.id = b.id
WHERE b.otherField = 'bla';

There are also some cases where it is mandatory to use like when you use derived tables, such as:

SELECT * 
FROM (
    SELECT * 
    FROM Products_Orders_items a
    INNER JOIN Customers_orders_items b ON a.id = b.id
    WHERE b.otherField = 'bla'
) a

If you don't use an alias here, the query will give you an error.