Qfyilyi

I have written this piece of SQL, I know there are ways to make it run faster, with the right practices.

SELECT DISTINCT

    ACCOUNTNUM,

    FIRSTNAME AS NAME, 

    LASTNAME AS SURNAME, 

    (PHONE + ' ' + CELLULARPHONE) AS PHONENUM, 

    EMAIL,

    (SELECT TOP 1 CREATEDDATE 

     FROM RBOTRANSACTIONTABLE 

     WHERE CUSTACCOUNT = ACCOUNTNUM 

     ORDER BY CREATEDDATE DESC) AS LASTVISIT,  -- LAST VISIT, 

    (SELECT COUNT(TRANSACTIONID) 

     FROM RBOTRANSACTIONTABLE 

     WHERE CUSTACCOUNT = ACCOUNTNUM) AS TOTALVISITS, -- TOTAL VISITS, 

    (SELECT SUM(PAYMENTAMOUNT) 

     FROM RBOTRANSACTIONTABLE 

     WHERE CUSTACCOUNT = ACCOUNTNUM) AS TOTALSALES, -- TOTAL SALES,  

    (SELECT SUM(DISCAMOUNT) 

     FROM RBOTRANSACTIONTABLE 

     WHERE CUSTACCOUNT = ACCOUNTNUM) AS DISCOUNT

FROM 

    CUSTOMER 

LEFT OUTER JOIN 

    RBOTRANSACTIONTABLE ON CUSTACCOUNT = ACCOUNTNUM

I know if I am using some sort of Joins, I don't have necessarily have to keep saying FROM RBOTRANSACTIONTABLE every time (the code after Email column). The code above works great for my requirement, but I know there are missing gaps in my knowledge, I just don't know what.

I am looking for in-depth answers as to why the above solution is not recommended, and why your solution is.

You may rewrite your query as a join to a subquery which finds aggregates:

SELECT

    c.ACCOUNTNUM,

    c.FIRSTNAME AS NAME,

    c.LASTNAME AS SURNAME,

    (c.PHONE + ' ' + c.CELLULARPHONE) AS PHONENUM,

    c.EMAIL,

    a.LASTVISIT,

    COALESCE(a.TOTALVISITS, 0) AS TOTALVISITS,

    COALESCE(a.TOTALSALES, 0) AS TOTALSALES,

    COALESCE(a.DISCOUNT, 0) AS DISCOUNT

FROM CUSTOMER c

LEFT JOIN

(

    SELECT

        CUSTACCOUNT,

        MAX(CREATEDDATE) AS LASTVISIT,

        COUNT(TRANSACTIONID) AS TOTALVISITS,

        SUM(PAYMENTAMOUNT) AS TOTALSALES,

        SUM(DISCAMOUNT) AS DISCOUNT

    FROM RBOTRANSACTIONTABLE

    GROUP BY CUSTACCOUNT

) a

    ON c.ACCOUNTNUM = a.CUSTACCOUNT;

You should always use proper aliases when referring to columns in the SELECT clause.

Tim's is definitely a good way to rewrite the query. But you can also make your version more efficient by getting rid of the count(distinct) and outer join:

SELECT ACCOUNTNUM,

       FIRSTNAME AS NAME, 

       LASTNAME AS SURNAME, 

       (PHONE + ' ' + CELLULARPHONE) AS PHONENUM, 

       EMAIL,

       (SELECT TOP 1 CREATEDDATE FROM RBOTRANSACTIONTABLE WHERE CUSTACCOUNT = ACCOUNTNUM ORDER BY CREATEDDATE DESC) AS LASTVISIT,  -- LAST VISIT, 

       (SELECT COUNT(TRANSACTIONID) FROM RBOTRANSACTIONTABLE WHERE CUSTACCOUNT = ACCOUNTNUM) AS TOTALVISITS, -- TOTAL VISITS, 

       (SELECT SUM(PAYMENTAMOUNT) FROM RBOTRANSACTIONTABLE WHERE CUSTACCOUNT = ACCOUNTNUM) AS TOTALSALES, -- TOTAL SALES,  

       (SELECT SUM(DISCAMOUNT) FROM RBOTRANSACTIONTABLE WHERE CUSTACCOUNT = ACCOUNTNUM) AS DISCOUNT

FROM CUSTOMER c;

With the right indexes, this could even have better performance than the group by/join version.