Is subquery most efficient in this way?

Question

Here is my code, I am trying to get the total sum of prices of books which appear in the order made by 'Village bookshop' on the '27/09/2018'

Relevant tables

book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating)

bookShop(shopNo, shopName, street, city, county)

orders(orderNo, sNo, oDate, salesRep)

orderDetails(oNo, bookISBN, quantity)

My question is, is there a more efficient way than a subquery to do this?

SELECT sum(rrPrice)
FROM   book 
WHERE  isbn in(
    SELECT bookISBN
    FROM   orderDetails INNER JOIN orders ON orderNO = oNO 
           INNER JOIN bookShop on sNO = shopNo
    WHERE  shopName = 'Village bookshop' and oDate='27/09/2018'
)

Sorry, I have a question unrelated to your question. What is the reason to `SUM(rrprice)` ? It makes no sense to me. — Alex, Nov 12 '18 at 16:14
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should be `SUM(rrprice*quantity)` but without quantity - that is useless info. — Alex, Nov 12 '18 at 16:20
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates. — Strawberry, Nov 12 '18 at 16:37
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 — philipxy, Nov 12 '18 at 20:58

score 0 · Accepted Answer · answered Nov 12 '18 at 16:18

There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.

It will be something like:

SELECT sum(rrPrice)
FROM   book 
WHERE  EXISTS (
    SELECT *
    FROM   orderDetails od INNER JOIN orders ON (orderNO = oNO) 
                        INNER JOIN bookShop ON (sNO = shopNo)
    WHERE  shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)

It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.

ScaisEdge · Answer 2 · 2018-11-12T16:47:22.347

0

Normally a inner join on a subquery work better that an IN clause on the same subquery so try using

    SELECT sum(rrPrice)
    FROM   book 
    INNER JOIN (
        SELECT bookISBN
        FROM   orderDetails 
        INNER JOIN orders ON orderNO = oNO 
        INNER JOIN bookShop on sNO = shopNo
        WHERE  shopName = 'Village bookshop' and oDate='27/09/2018'
    ) t on book.isbn = t.bookISBN

The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content

edited Nov 12 '18 at 16:47

answered Nov 12 '18 at 16:19

ScaisEdge

131,976
10
91
107

1

your last query will bring different result vs original one. Original one takes `book.rrprice` one time per `book.isbn`. But your query `INNER JOIN orderDetails` which means we can and will have many records with same `orderDetails.bookISBN = book.isbn` so `SUM` will multiply randomly. Hmm... and seem 1st query has the same issue... – Alex Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear. – philipxy Nov 12 '18 at 21:00

TiddlyWiddly · Answer 3 · 2018-11-12T16:20:21.023

-2

EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.

You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:

SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'

edited Nov 12 '18 at 16:20

answered Nov 12 '18 at 16:12

TiddlyWiddly

5
4

2

It is bad practice to put join conditions in a `where` clause. – trincot Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks – TiddlyWiddly Nov 12 '18 at 16:16
2

*Leaving this up for others to learn from* - Sorry but people should learn from best examples and best practices but not from random wrong ones. – Alex Nov 12 '18 at 16:22

Is subquery most efficient in this way?

3 Answers3