How can i avoid an extra inner join to optimize this query?

Question

Is there any way where I can avoid the inner join to get the customer firstname, and lastname from customer table and how can i optimize the execution plan?

SELECT c1.firstname, c1.lastname, t.*
FROM customer c1
INNER JOIN
    (select c.Customerid ,count(si.CustomerID) as 'No Of Orders'
    from Customer c
    LEFT OUTER join SalesInvoice si ON si.CustomerID = c.CustomerID
    GROUP by c.CustomerID) as t
ON c1.CustomerID = t.Customerid

enter image description here

Why do you think INNER JOIN is a bad thing? This query is better then grouping by firstname, lastname. COUNT() OVER() should be roughly as good as your one. I'd do Customer LEFT JOIN (select CustomerID, count(*) cnt from SalesInvoice GROUP BY CustomerID) if your purpose is brevity. — Alsin, Mar 25 '13 at 08:48

score 2 · Accepted Answer · edited May 23 '17 at 12:13

There could be 2 ways to do it. One way is to include all non-aggregate columns into the group by list as below:

SELECT
    c.firstname,
    c.lastname,
    c.CustomerId,
    count(si.CustomerID) as 'No Of Orders'
FROM
    customer c
LEFT OUTER JOIN
    SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
    c.CustomerID,
    c.firstname,
    c.lastname

Another way to do it, would be to use an aggregate function (min or max should do) in the select list:

SELECT
    MIN(c.firstname) as firstname,
    MIN(c.lastname) as lastname,
    c.CustomerId,
    count(si.CustomerID) as 'No Of Orders'
FROM
    customer c
LEFT OUTER JOIN
    SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
    c.CustomerID

I think the first query may be better in performance. To improve overall performance you will need to create an included or covering index.

You can have a look at the following links to give you an idea on included indexes:

Aleksandr Fedorenko · Answer 2 · 2013-03-25T09:04:32.713

1

Try this option with OVER() clause

SELECT c1.Customerid, c1.firstname, c1.lastname, 
       COUNT(si.CustomerID) OVER(PARTITION BY c1.CustomerID) AS 'No Of Orders'
FROM customer c1 LEFT JOIN SalesInvoice si ON si.CustomerID = c1.CustomerID

For improving performance you need this index

CREATE INDEX ix_Customerid_Customer ON Customer(Customerid) INCLUDE(firstname, lastname)

edited Mar 25 '13 at 09:04

answered Mar 25 '13 at 08:21

Aleksandr Fedorenko

16,594
6
37
44

This will repeat the customerid, firstname – Vishwanath Dalvi Mar 25 '13 at 08:27
@Vishwanath Dalvi In example OP the customerid, firstname repeat too;) – Aleksandr Fedorenko Mar 25 '13 at 08:29

score 0 · Answer 3 · answered Mar 25 '13 at 08:11

Try that one:

SELECT
    c.firstname,
    c.lastname,
    c.CustomerId,
    count(si.CustomerID) as 'No Of Orders'
FROM
    customer c
LEFT OUTER JOIN
    SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
    c.CustomerID,
    c.firstname,
    c.lastname

How can i avoid an extra inner join to optimize this query?

3 Answers3