How to Create a Table Alias in MySQL

How do I create a table alias in MySQL

Off the top of my head

CREATE VIEW dbo_customers AS
SELECT * FROM customers

Maybe not the best solution but should work as the view is updatable. Will definitely work for Read Only

Make an alias column from 2 different tables in MySQL

We can try the following join query:

SELECT
    i.code,
    i.total,
    COALESCE(e.name, m.name) AS name
FROM inventory i
LEFT JOIN electricity e
    ON i.code REGEXP CONCAT('el_.*_', e.id, '$')
LEFT JOIN mobile m
    ON i.code REGEXP CONCAT('mob_.*', m.id, '$');

Demo

The above query uses a COALESCE trick to choose the correct name for each item, which assumes that a given item would only match to either the electricity or mobile table.

But, your database design is not ideal. It would be much better to just have a single table containing metadata for both mobile and electrical (and other) items. In addtion, your tables should have proper join columns which don't require complex substring or regex operations to match. I suggest the following:

inventory
+----+------------+-----------+
| id |    code    |   total   |
+----+------------+-----------+
| 1  | el_pr_25   |     45    |
| 2  | el_pr_11   |     33    |
| 3  | el_pr_x73  |     25    |
| 4  | mob_tp_x93 |     23    |
| 5  | mob_tp_t55 |     33    |
| 6  | mob_tp_25  |     22    |
+----+------------+-----------+

items
+--------------+----------+-------------+
| inventory_id | name     | type        |
+--------------+----------+-------------+
| 1            | test1    | electricity |
| 2            | test2    | electricity |
| 3            | test3    | electricity |
| 4            | test 66  | mobile      |
| 5            | test 222 | mobile      |
| 6            | test 323 | mobile      |
+--------------+----------+-------------+

MySQL alias and Table name

In the first query:

SELECT *
FROM `pd_cheque` as p
WHERE `pd_cheque`.etype='pd_issue'

You have renamed the table using a table alias. Everywhere else in the query, you need to use p to refer to the table. The query no longer recognizes the original table name, because it has been renamed.

Table aliases are required for self-joins. Consider:

select
from t join
     t
     on t.id = t.otherid

It just doesn't make sense without aliases:

select
from t join
     t tother
     on tother.id = t.otherid;

In addition, table aliases make queries easier to read.

How to produce query results with automatic table aliases in MySQL?

MySQL does not create qualified aliases like that. If you don't explicitly name aliases, you will have duplicate column names in the result, if the select-list includes columns with the same name in multiple tables.

You don't necessarily have to make aliases for all the columns, only the ones you need to differentiate.

You don't have to forego the wildcard, but you should limit the wildcard to specific tables, for which you don't need to make aliases.

SELECT C.*, P.ID AS P_ID, P.NM AS P_NM
FROM PROVINCE P JOIN COUNTRY C ON C.ID = P.CTID

MySQL INSERT with table alias

Use back-tick to escape reserved words.

  INSERT INTO `attributeStrings` (`ItemID`, `Key`,`Value`) VALUES (3,'Categories','TechGUI')

Looks like insert does not support alias. see here

---

Edit: ok, the MySQL ref says no alias in insert

---

It does work

mysql> INSERT INTO `attributeStrings` (`ItemID`, `Key`,`Value`) VALUES (3,'Categories','TechGUI');
Query OK, 1 row affected (0.03 sec)

mysql> select * from attributeStrings;
+--------+------------+---------+
| ItemId | Key        | Value   |
+--------+------------+---------+
|      3 | Categories | TechGUI |
+--------+------------+---------+
1 row in set (0.00 sec)

---

Related Topics