Query using cross join or inner join

Question

Please consider the following three tables that I am playing with in the image below:

I want to come up with a SQL Query which shows first_name, last_name, and the total amount of all orders for customer_id 12345

Here is what I have come up with:

SELECT C.first_name , C.last_name,
       SUM(O.total_price) AS TotalAmount
FROM   Customer C , Order O
WHERE  C.customer_id = O.customer_id
AND    C.customer_id = 12345;

Does my query look good? If yes, then should I be using Inner Join instead of Cross Join from an efficiency point of view?

Answer 1

Your query looks OK, for the most part. There are two things you could do better.

---

C and O don't make for very good aliases. Picture if you had 20 tables instead of just 2. In programming it's better to give aliases, variables, etc. meaningful names. Aliases are useful for shortening long table names, but should not be short to the point it obfuscates your code. In your case something like Cust and Ord would be better.

---

You are using old-style join syntax which is deprecated and should be avoided. In your case, inner join would work good, so instead of this:

FROM   Customer C , Order O
WHERE  C.customer_id = O.customer_id
AND    C.customer_id = 12345;

You should do this:

FROM   Customer C
INNER JOIN Order O
  ON C.customer_id = O.customer_id
WHERE C.customer_id = 12345;

---

Cross join

Chances are, if you think you need a cross join, you probably don't need a cross join. First, they are insanely slow. Second, the cross join gives you a Cartesian product which is almost never what you want, and in your case your results would be way off. (Try it!)

Here is some more information about cross join in SQL, for further reading.

Answer 2

SELECT C.first_name , C.last_name,
       SUM(O.total_price) AS TotalAmount
FROM   Customer C , Order O
WHERE  C.customer_id = O.customer_id
AND    C.customer_id = 12345;

This spacing is very bothersome to me.

SQL select statements have up to 6 distinct clauses. They are:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

These keywords should standout. The way we make them stand out is align them to the left-most indentation of the query. Everything else is indented from here.

By just applying this rule, our query looks like this:

SELECT C.first_name , C.last_name,
       SUM(O.total_price) AS TotalAmount
FROM   Customer C , Order O
WHERE  C.customer_id = O.customer_id
       AND C.customer_id = 12345;

This is already better, but I still don't like how the rest is lined up. I'll fix that in the next example.

We should be explicit with our join types. To be honest, I have no clue what type of join you're actually using there. I would have to look it up, and it may even be database specific. So let's fix our FROM clause (and pay attention to some other spacing changes I make):

SELECT C.first_name, C.last_name,
    SUM(O.total_price) AS TotalAmount
FROM Customer C
    INNER JOIN Order O ON O.customer_id = C.customer_id
WHERE C.customer_id = 12345;

Finally, this query is pretty simple, so the following suggestion is somewhat trivial. However, other queries, may not be so simple. And they may be significantly longer. A good habit to get in might be using variables.

DECLARE @CustomerID int;
SET @CustomerID = 12345;

SELECT C.first_name, C.last_name, SUM(O.total_price) AS TotalAmount
FROM Customer C
    INNER JOIN Order O ON O.customer_id = C.customer_id
WHERE C.customer_id = @CustomerID;