Why use letters in front of each value in MySQL query?

Question

Why would I use letters in front of each value in my query like this? In the database, each of these values is WITHOUT the letter in front.

    SELECT c.client_id, c.client_name, c.contactperson, c.internal_comment, 
    IF NULL(r.region, 'Alle byer') as region, c.phone, c.email,
    uu.fullname as changed_by, 
    (select count(p.project_id) 
        from projects p 
        where p.client_id = c.client_id and (p.is_deleted != 1 or p.is_deleted is null)
    ) as numProjects 
    FROM clients c LEFT JOIN users uu ON c.db_changed_by = uu.id 
    LEFT JOIN regions r ON c.region_id = r.region_id 
    WHERE (c.is_deleted != 1 or c.is_deleted is null)

I have tried looking it up, but I can't find it anywhere.

Read this http://stackoverflow.com/questions/198196/when-to-use-sql-table-alias — , May 12 '15 at 11:21

score 4 · Accepted Answer · edited Apr 23 '20 at 16:14

4

When in SQL you need to use more than one table for a query, you can do this:

SELECT person.name, vehicle.id FROM person, vehicle;

OR you can do it smaller, and put like this

SELECT p.name, v.id FROM person p, vehicle v;

It's only for reducing the query length, and it's useful for you

edited Apr 23 '20 at 16:14

Dharman

30,962
25
85
135

answered May 12 '15 at 11:06

Kyto

181
3
12

Are you sure - `Its only for reduce the query lenght`? – Sougata Bose May 12 '15 at 11:07
1

Smaller means that you use ALIAS – B001ᛦ May 12 '15 at 11:07
The accepted answer is not accurate. You should consider checking the answers below. – qualebs Mar 05 '22 at 23:01

score 3 · Answer 2 · answered May 12 '15 at 11:08

By "letters in front", I assume you mean the qualifiers on the columns c., uu. and so on. They indicate the table where the column comes from. In a sense, they are part of the definition of the column.

This is your query:

SELECT c.client_id, c.client_name, c.contactperson, c.internal_comment,
       IF NULL(r.region, 'Alle byer') as region, c.phone, c.email,
       uu.fullname as changed_by,
       (select count(p.project_id)
        from projects p
        where p.client_id = c.client_id and (p.is_deleted != 1 or p.is_deleted is null)
       ) as numProjects
FROM clients c LEFT JOIN
     users uu
     ON c.db_changed_by = uu.id LEFT JOIN
     regions r
     ON c.region_id = r.region_id
WHERE (c.is_deleted != 1 or c.is_deleted is null)

In some cases, these are needed. Consider the on clause:

 ON c.region_id = r.region_id

If you leave them out, you have:

ON region_id = region_id

The SQL compiler cannot interpret this, because it does not know where region_id comes from. Is it from clients or regions? If you used this in the select, you would have the same issue -- and it makes a difference because of the left join. This is also true in the correlated subquery.

In general, it is good practice to qualify column names for several reasons:

The query is unambiguous.
You (and others) readily know where columns are coming from.
If you modify the query and add a new table/subquery, you don't have to worry about naming conflicts.
If the underlying tables are modified to have new column names that are shared with other tables, then the query will still compile.

score 1 · Answer 3 · answered May 12 '15 at 11:09

Consider you are accessing 2 tables and both have same column name say 'Id', In query you can easily identify those columns using letters like a.Id == d.Id if first table has alias name 'a' and second table 'b'. Or else It would be very difficult to identify which column belongs which table especially when you have common table columns.

Why use letters in front of each value in MySQL query?

3 Answers3