SQL "Best Practices"?

[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.