Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Checking the efficiency of my query?

    Suppose I have two tables(Customer and Order) which are as follows:

    Code:
    Customer     
    customer_id
    first_name
    last_name
    address_1
    address_2
    city
    state
    country
    zip
    discount
    active
    
    Order
    order_id
    date
    customer_id
    product_id
    billing_name
    billing_addr1
    billing_addr2
    billing_city
    billing_state
    billing_country
    billing_zip
    quantity_sold
    total_price
    I am looking to find firstname, lastname and total amount of all orders for customer_id 4857.

    Could you tell me if my query is correct from efficiency point of view:

    Code:
    SELECT    Customer.firstname,
    		          Customer.lastname,
                              SUM(Order.total_price)
                      FROM Customer
                                     INNER JOIN Order ON  Customer.customer_id = Order.customer_id
                      WHERE  Customer.customer_id = 4857;
    Another thing I am concerned about is that in the line INNER JOIN Order ON Customer.customer_id = Order.customer_id , I have written Customer.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign?
    Last edited by Jack_Tauson_Sr; 02-21-15 at 18:22.

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    And you need to read and download the PDF for:
    https://www.simple-talk.com/books/sq...l-code-smells/

    Everything is wrong! A table is a set, so the _name is a plural or collective noun. I used a credit card number for the customer_id. I follow USPS rules for the address data. I included some of the constraints needed, so this is a minimal skeleton.

    Did you know that DATE is a reserved word in SQL? And it is too generic to be used anyway!
    What is the vague adjective “active”? Surely you did not use a bit flag in SQL! What is discount? A rate? an amount? A discount class? Until we have specs, I am dropping them:

    CREATE TABLE Customers
    (customer_id CHAR(16) NOT NULL PRIMARY KEY,
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    address_1 CHAR(35) NOT NULL,
    address_2 CHAR(20) NOT NULL,
    city_name CHAR(25) NOT NULL,
    state_code CHAR(2) NOT NULL,
    country_code CHAR(3) NOT NULL,
    zip_code CHAR(5) NOT NULL);

    I am sorry you have only one order, but that is what a singular _name says! You do not understand how to model a strong and weak entity. We need DRI actions.

    CREATE TABLE Orders
    (order_id CHAR(20) NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL
    customer_id CHAR(16) NOT NULL
    REFERENCES Customers (customer_id ),
    billing_name ..
    billing_addr1..
    billing_addr2..
    billing_city_name..
    billing_state_code ..
    billing_country_code..
    billing_zip_code ..);

    Why did you invent your own product_id? We all kinds of industry standards (I like GTIN). This table is the weak entities. See the DRI?

    CREATE TABLE Order_Details
    (order_id CHAR(20) NOT NULL
    REFERENCES Orders(order_id)
    ON DELETE CASCADE,
    gtin CHAR(15) NOT NULL,
    PRIMARY KEY (order_id, gtin),
    unit_cost DECIMAL (8,2) NOT NULL
    CHECK ( unit_cost >= 0.00),
    order_qty INTEGER NOT NULL
    CHECK (order_qty > 0));

    This is a common idiom in SQL and you should have seen it in your data modeling class. Putting the total in the Orders table is called a “non-normal form redundancy” and it is common among noobs with a COBOL background. Since you put one column per line, like a punch card, I will guess you are an old COBOL programmer or were trained by one.

    >> Could you tell me if my query is correct from efficiency point of view: <<

    Correct and effective are not the same thing. Your design is wrong. Your query did no totals.

    SELECT C.first_name, C.last_name,
    SUM(D.unit_price * order_qty) AS cusomer_order_tot
    FROM Customers AS C, Order_Details AS
    WHERE C.customer_id = D.customer_id
    AND C.customer_id = '4857'
    GROUP BY C.first_name, C.last_name;

    >> INNER JOIN Order ON C.customer_id = D.customer_id*, I have written C.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign? <<

    There is no difference. Notice that I did not use the infixed inner join syntax; there is also no difference with it. However, the infixed syntax shows a linear mindset rather than a declarative programmer's mindset. That ids a bit harder to understand but it is a guide I uzse to look for errors when I am correcting SQL.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the SQL engine will not care which side your column is on in an inner join with regards to performance.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Celko is correct that you need a GROUP BY to aggregate your totals.
    The only other thing I would suggest is that your query will return no records at all if the customer has no orders, when perhaps returning $0 would be better.
    You can do this by left outer joining to your Orders table and using coalesce(SUM(Order.total_price), 0) for your calculation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •