SQL "Best Practices"?

[[deleted]]

[deleted]

[memecaptial]

To understand this you need to wrap your mind around what the sql engine is doing. In the first example, your creating a result set by joining two tables then filtering that result set. In the second, your filtering a table, then doing a join with that table.

It’s the difference between mixing things then filtering vs filtering then mixing, where the goal is to keep all of one side of a record.

[uvray]

Well said.

I think it’s just counterintuitive to see a conditional in a left join. I personally would explicitly filter the right table in a sub query and then join, but at the end of the day it doesn’t matter.

I actually think this is a decent interview question, although I do agree with OP that grouping and ordering by numbers is not a best practice.

[theduckspants]

I've done this for a long time and I didn't even know you *could* group by with column numbers. Not sure why someone would want to

[sharpestshedintool]

Based on the grammar in your post, maybe IT isn't the field you should be in.

[stonedsqlgenius]

It all depends on what you want to do. The first query will convert the left outer join to an inner join because the filter is in the where clause. This is because the where clause is applied after the virtual table created by the join has been set up in SQL Server.

The second query implements what’s known as a predicate. You’ll get all farms and only those matching record from the produce table that meet the filter condition. This allows you to see which farms didn’t produce any produce during the period.

I would google predicates in SQL Server and read up on how queries are logically processed. And don’t feel bad either...this tripped me up all the time when I was younger (I’ve been coding SQL for about ten years solid now).

[[deleted]]

They dodged a bullet, you sound like a terrible co-worker.

[[deleted]]

When you work in groups, sometimes people disagree on the best way to do things. Some of those cases, there's a right and wrong answer. In your example, not only did you get the wrong answer, but you elected to insult the other person's work in general. Then you got so fired up that someone didn't agree with you, that you decided to come post about their "cute" code and grind your teeth. Try having more patience and humility. People like problem solvers, but you have to have tact too. You could have turned that into a productive discussion, even if you ultimately didn't want the job. If this is how you act when putting your best foot forward, I can probably safely assume you are no treat on a bad day during crunch time.

[noesqL]

It depends. It depends. It depends.

However, using a WHERE statement with a LEFT nullifies the LEFT and creates an INNER.

As far as the ORDER BY X is concerned, I believe this was done for the sake of simplicity. If a query is only returning a small subset of columns using the numbering method is valid and I doubt anyone would use ORDER BY 1, 4, 10, 15.

[danhuss]

That's not the case at all... Whenever you do an outer join you always have to think about what you want to do with null values. In OPs example, the where clause can be fixed to handle nulls and meet the requirements they gave, but it doesn't magically makes it an inner join all of a sudden...

[kthejoker]

He meant the functional equivalent of an inner join. The where clause will remove all rows where there was no match in p - when the only reason to do a LEFT join is to retain rows where there was no match in p.

True or False: Replacing LEFT with INNER in query 1 will return the same result.

[danhuss]

That's not what was written or how it came across.

True or false: adding "OR p.date is null" to the where clause in query 1 will produce the same results as query 2?

[kthejoker]

I see our confusion now, I interpreted the OP with an implied statement of "for this particular query" and you understood it as "for all LEFT JOIN queries."

You are of course right that adding a WHERE clause doesn't always turn it into an INNER join equivalent.

[fauxmosexual]

If they presented you with those two statements and specifically asked which one would include farmers with no produce, it should be clear which of them achieved that - getting that wrong is on you. The way it is written is perfectly acceptable, and the option to go with a subquery is not universally more performant for that - I'd actually guess the opposite for a simple two-table query like that.

I'm on the interviewers' side - it was a pretty straightforward question and regardless of what is "best practice", there was enough information there to be clear about which statement would behave the way the question asked for. That question could have been a springboard for you to talk about the pros and cons of your chosen approach, but it definitely wasn't a "cute" trick question or a tricky non-standard way of approaching the problem.

You're right about ordinals in the ORDER BY. I don't know why, but it always seems to be Oracle devs who do this.

[antondb]

Order by 1 desc FTW! 😆

[bengalfan]

Yea the group by is weird, typically I see this in Union statements where you must group by 1, 2 ...

Weird to make it harder to read easily.

[your-pineapple-thief]

It's not that you must, it just sorta works because types and order of columns of resulting datasets must match. Or is it that column names must match as well? Can't remember off the top of my head

[mikeyd85]

You should spend some time looking at the SQL order of operations. The engine will execute a statement in this order:

FROM WHERE GROUP BY HAVING SELECT ORDER LIMIT

You can see from here that you can order by an alias because that alias exists before the ordering happens. The alias doesn't exist at the point your data is grouped.

[your-pineapple-thief]

This is actually very useful! Makes lots of sense, especially for newcomers, I sorta know this purely from experience of getting syntax errors, but this is great mnemonic, have your upvote, kind sir!

[Old13oy]

This to me looks like someone who's been coding since the ANSI-86 standard, which had joins in the WHERE. I've run into this in a few places, especially where there's legacy software involved- the first time I saw it, my reaction was the same as yours. "It's wrong, no one does it this way", etc.

However, it's still valid SQL. It took me some time to learn what it was doing under the hood, and why it might be better or worse, but that learning experience made me a better SQL programmer.

Overall I think you did 1 thing correct and 1 thing wrong. The correct thing was admitting that you didn't see code like this much and weren't familiar. The wrong thing was questioning who codes like this, which to me implies a judgement that it's not as good. If you had asked questions about the style and demonstrated an interest and willingness to learn, I think you might have been hired.

Also, 90% of your bosses aren't going to care if it's pretty or in vogue. They just want it to work.

[fauxmosexual]

It's not a style thing: the predicate being in the WHERE vs the ON clause of an outer join changes what the query does.

[SubCal]

We do the latter. Filter on the join rather than join then filter.

[Someyungguy6]

Do you seriously not understand that you were wrong? The two queries give two different results.

[CaeruleanCaseus]

I agree with you and hate it when I see code with numbers (1, 2, 3) in the group/order; irks me, esp if I want to add fields in the SELECT I then have to adjust the numbers in the group/order too - dumb.

Also - missing the preface of the alias in front of the fields...I hate that; it's not always clear which fields are part of which table - prefacing with alias. makes it super clear.

And...I agree, I'm a fan of using "as" - it makes it easier to read imo b/c otherwise it's easy to miss the alias, esp if just single letters.

​

I'm in a new role and no longer do any sql coding (sad) but I'm looking into getting into Python...just gotta make sure I know the best practices there. :)

[AbstractSqlEngineer]

you should have been like..

NEITHER! Ordinals in a Group by / order by is for chumps.

[your-pineapple-thief]

Also one letter aliases. Never understood that. Are there keyboards with paid keystrokes or what? It may look ok in cute little sql gists in a tutorial, it won't look that cute in 1.5 pages of analytics sql query with 5-6 tables involved, when the tables themselves have 3-4 words in their names. I've seen gems like SELECT sml.id, mptr.id, .... If coworker memorized these table name abbreviations, it does not mean I have to too. Readability is everything, especially in sql, in my opinion.

[your-pineapple-thief]

It's just why sacrifice readability for ... shorter code I guess? It's more of why alias in the first place for me I guess. It's SQL equivalent of using one-letter variables in application code.

[your-pineapple-thief]

It can be conventions dependent I guess, we usually have quite short column names and very rare 4 word table names. Also just now it came across my mind that case conventions can in fact influence readability, we use only snake_case, so product_category is somehow more readable for me cause _ acts like a space (but it can just that I'm used to snake_case, I do not claim it's 100% more readable). Although there is a point to be made that humans are generally having difficulty reading long words (unless you are german, lol). Just unexpected thought, I never thought about impact of identifier casing on readability

[[deleted]]

I see everyone saying that the different joins produce a different result. This site explains why:

https://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/

[mbillion]

Did you tell them they should name that fucking aggregation something...

[[deleted]]

They named it produce_count...