4

MySQL ver 10 (MariaDB). PHP 5.6.3 libmysql 5.1.73

It's been a while for me working with Oracle but I vaguely remember that Oracle did exactly what I'm expecting in this example. I could be mistaken or maybe MySQL just isn't doing the same thing... The example is created for this question, so if you see a syntax issue, it's related to that.

Assuming a simple schema like this:

Table COUNTRY
ID
NM

Table PROVINCE
ID
NM
CTID

I was hoping that this query:

SELECT * FROM PROVINCE P JOIN COUNTRY C ON C.ID = P.CTID

Would produce the following output:

P.ID | P.NM | P.CTID | C.ID | C.NM

Unfortunately, the output is without table aliases and columns from joined table that are in the selected table are missing from results (only one ID column in results). Like this:

ID | NM | CTID

Is there a way to get the aliased output shown above? Or is there some other way to get all five columns in the results without having to use anything like P.ID as P_ID explicitly in the query?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
jacekn
  • 1,521
  • 5
  • 29
  • 50
  • 1
    Possible duplicate of [In a join, how to prefix all column names with the table it came from](https://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from) – Pinke Helga Mar 07 '19 at 01:16
  • I cannot reproduce `ID | NM | CTID` with your given query using `ON` clause. It produces **all** columns without table names. Only when applying the `USING` cause instead, there are no duplicate columns since `USING` joins on equality out of the box. The question details should be more precise. – Pinke Helga Mar 07 '19 at 01:28

1 Answers1

1

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828