Query using cross join or inner join

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?

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.

StackExchange Code Review Q#82314, answer score: 6