More efficient way to write a query

Question

Here is my working Query :

SELECT 
    public."T1"."Id"    AS "StuffId",
    public."T1"."Name"  AS "StuffName",
    public."T3"."Id"    AS "ThingId",
    public."T3"."Name"  AS "ThingName",
    public."T4"."Id"    AS "AnyId",
    public."T4"."Name"  AS "AnyName"
FROM public."T1"
INNER JOIN  public."T2"
    ON public."T1"."Id" = public."T2"."StuffId"
INNER JOIN public."T3"
    ON public."T2"."Id" = public."T2"."ThingId"
INNER JOIN public."T4"
    ON public."T4"."Id" = public."T2"."AnyId"

I want to write it in a more readable way with aliases but for the alias is never acknowledge.

I am pretty knew to postgresql but I have a very good knowledge of SQL Server, My SQL and Oracle.

This is what I kinda want :

SELECT 
    Stuff.Id    AS "StuffId",
    Stuff.Name  AS "StuffName",
    Thing.Id    AS "ThingId",
    Thing.Name  AS "ThingName",
    Any.Id  AS "AnyId",
    Any.Name    AS "AnyName"
FROM public."T1" AS Stuff
INNER JOIN  public."T2" AS Link
    ON Stuff.Id = Link.StuffId
INNER JOIN public."T3" AS Thing
    ON Thing.Id = Link.ThingId
INNER JOIN public."T4" AS Any
    ON Any.Id = Link.AnyId

What should I change in my first query to have something like that?

Answer 1

The first thing you want to do is get rid of all these noisy quotes around identifiers. In standard SQL, identifiers are considered case-insensitive (and default to upper case), so these 3 (unquoted) qualifiers are syntaxically equivalent :

... as FOO
... as foo
... as FoO

Quoted identifiers are there to be used when mixed cases is really required or when identifiers clash with reserved keywords.

The problem in your use case is that you cannot remove the quotes from your query, because your tables were initially created with (quoted) camelCase identifiers, like :

CREATE TABLE T1 (
    "Id"    INT,
    "Name"  VARCHAR(100)
);

You need to RENAME these columns to something without quotes :

ALTER TABLE T1 RENAME "Id" TO id;
ALTER TABLE T1 RENAME "Name" TO name;

To replace case breaks in camelCase notation, I would recommend using the underscore character :

ALTER TABLE T2 RENAME "StuffId" TO stuff_id;

Once all table columns are properly renamed then you can run the following query :

SELECT 
    Stuff.Id    AS Stuff_Id,
    Stuff.Name  AS Stuff_Name,
    Thing.Id    AS Thing_Id,
    Thing.Name  AS Thing_Name,
    Any.Id      AS Any_Id,
    Any.Name    AS Any_Name
FROM 
    T1 AS Stuff
    INNER JOIN T2 AS Link ON Stuff.Id = Link.Stuff_Id
    INNER JOIN T3 AS Thing ON Thing.Id = Link.Thing_Id
    INNER JOIN T4 AS Any ON Any.Id = Link.Any_Id