SQL just feels like its screaming at me....

Both queries show a severe lack of understanding SQL.

That's why I always write my queries in lowercase on, well...

T-SQL, MySQL, Postgres, ...

Well, anything supports it and it doesn't look like screaming anymore.

@IntrusionCM i mean i agree it could be better, but it still works..

Out of curiosity, what would've you done instead in the second query?

@IntrusionCM elaborate?

First Query: You select and Groupon By Order ID.

Of a single table.

Yet you select by COUNT * meaning all rows.

Assuming foreign key or DB specifics like InnoDBs BTree, the more logical approach would be the COUNT on the sequence / primary key as it must be related somehow.

In this specific context it could be a major speedup.

Second query:

You do left joins.

P - T - O - C

All left.

Yet the given where clause enforces that an entry in C has to be non nil / to exist, as such an inner join is not only more appropriate but can lead to significant speedup too.

@IntrusionCM

The goal here was not speed. There were certain tasks to achieve.

1) needed to show order id and the count (literally the instructions). Also, the table had no primary key defined (go figures, it's the Profs code)

2) sure I'd concede, but the requirements given in the full document asked to ensure that in a case of an empty join I could still return the product info.

You made a few assumptions on what exists....

@gitoutofhere count(index) is preferred than count(*)

Both selected col are in 1 table, you could just select customer then inner join

@gitoutofhere Sorry if u took this as an attack.

I read what was there and - in my opinion - it's not what you described.

1) a table wihtout a primary key... Ehm. In reality this makes either no sense or shows a severe lack of understanding databases, as you define a quantity of values without an identifier...

2) Show IDs and names of customers who purchased XY

There is nothing in the task definition that mentions that you should be able to return the product info.

Yes. I was making assumptions based on what you have written.

The primary purpose of SQL is imho to query for a specific set of information - a quantity of values - in relation to another quantity of values.

So the longer explanation, as in 'what leads to the speed' and is the basic foundation of SQL imho is:

How can I utilize a specific (quantity of) value (s) to only get the specific subset of values I need. A left join always leads to a cartesian product of all related values. That is not specific at all.

@IntrusionCM no I didn't. Sorry. I tend to write aggressively. You are right in the general regards, at work I wouldn't do this.

@gitoufofhere

Well if we are both sorry: Cheers, next beer / coffee / whateva you like goes on me :)

I'm sensitive in this area because I Had a Lot of interns who could pray the normal forms by heart and maybe even apply them / write queries, but they always missed the step of visualising / understanding what they are doing.

And - especially in complex databases - this is very important, because only then you understand why / how a query fails.

Wait til you forget, answer a quick IM and realize you screamed at someone.

Imagine ghe second example, but just the SQL keywords are lower case.

Now THAT would trigger me tfo.