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

Filipe Silva · Accepted Answer · 2013-12-03T22:04:28.520

9

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.

edited Dec 03 '13 at 22:04

answered Dec 03 '13 at 21:56

Filipe Silva

21,189
5
53
68

1

My vote should put your reputation into 5 digits. You owe me a beer. – Dan Bracuk Dec 03 '13 at 23:00
@DanBracuk. eheh. Thanks for the push :) – Filipe Silva Dec 03 '13 at 23:02
Thanks! Both were great answers, but you were here first and it is cool to be the one to push you into 5 digits (10,000+ points) doh... someone else beat me to it... – codacopia Dec 03 '13 at 23:50

Sergey Kalinichenko · Answer 2 · 2013-12-03T22:09:35.050

This syntax

FROM tablename p

or

JOIN tablename p

create an alias for a table name. In most cases, it is simply a way to shorten your statement, because you can use a shorter name in place of a full table name.

However, in some instances an alias is required: when you need to reference the same table more than once in the same query, you must create an alias to disambiguate your statement.

Specific examples where an alias is required are sub-queries on the same table and self-joins.

Here is an example of a query with a subquery:

SELECT
FROM Orders o
JOIN OrderItem oi on o.ID=oi.OrderID
JOIN Item i on i.ID=oi.ItemID
WHERE EXISTS (
    SELECT *
    FROM OrderItem oi2
    JOIN Item i2 ON i2.ID=oi2.ItemId
    WHERE i2.Description='BACKPACK'
      AND oi2.OrderID=o.ID
)

The above query finds all orders that contain a backpack. Note that Item and OrderItem tables need to be included twice each, so they have multiple aliases.

Here is example of a query with a self join:

SELECT
    e.Name as EmployeeName
,   m.Name as ManagerName
FROM EMPLOYEE e
OUTER JOIN EMPLOYEE m ON m.ID=e.ManagerID
ORDER BY m.Name, e.Name

Above, the same table EMPLOYEE is referenced twice in the same query, because it participates in two different roles - as an employee, and as a manager. Giving the table two aliases lets us disambiguate the join condition m.ID=e.ManagerID.

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

2 Answers2